PostgreSQL query with nested Object in Array & Object in Object together (merging 3 tables)
Published: 2021-08-15 23:24:17

How to create a query that has both Object in Object & Object in Array!

It wasn't easy because I wasn't able to use json_agg inside of json_agg.
So I needed to create table that only has nested array & object to place it inside of first json_agg

How I did it

```javascript
SELECT 
// my main table that has product id & result
  styles.product_id,
    json_agg(
      json_build_object(
        'style_id', styles.id,
        'name', styles.name,
        'original_price', styles.original_price,
        'sale_price', styles.sale_price,
        'default?', styles.default_style,
// Here, I used my pre-made photosId.photos and skusId.skus so I can avoid using json_agg inside of json_agg
	'photos', photosId.photos,
	'skus', skusId.skus
      )
    ) AS results
  FROM styles
// Here, I created my first pre-made objects in Array
  INNER JOIN(
// There are two lines in select which is style_id and photos. I'm going going to call photos to the main table. style_id only exist because I am going to need to group this by style_id & call only style_id that I need after ON.
    SELECT photos.style_id,
// creating my photos (obj in arr)
	  json_agg(
        json_build_object(
          'thumbnail_url', photos.thumbnail_id,
          'url', photos.url
        )
      ) AS photos
	FROM photos
// group it by style_id
	GROUP BY photos.style_id
// only find rows that matches my style_id with styles.id and name my whole table photosId so I can call it in main table
	) AS photosId ON photosId.style_id = styles.id
  INNER JOIN(
// creating my obj in obj table. I am also going to write two elements which is style_id and obj in obj called skus
    SELECT skus.style_id,
	  json_object_agg(
		skus.id,
	    json_build_object(
		  'quantity', skus.quantity,
          'size', skus.size
		)
	  ) AS skus
	  FROM skus
// group it by style_id
	  GROUP BY skus.style_id
// name whole table as skusId and only allow rows that matchs style_id with styles.id
  ) AS skusId ON skusId.style_id = styles.id
// only find rows that matches product_id
  WHERE styles.product_id = ${product_id}
// Group whole table by styles.product_id so I only have 1 table with all the matching product_id
  GROUP BY styles.product_id
```


How it's going to look like

```javascript
{
    "product_id": "1",
    "results": [
  	{
            "style_id": 1,
            "name": "Forest Green & Black",
            "original_price": "140",
            "sale_price": "0",
            "default?": true,
            "photos": [
  			{
                    "thumbnail_url": "urlplaceholder/style_1_photo_number_thumbnail.jpg",
                    "url": "urlplaceholder/style_1_photo_number.jpg"
                },
  			{
                    "thumbnail_url": "urlplaceholder/style_1_photo_number_thumbnail.jpg",
                    "url": "urlplaceholder/style_1_photo_number.jpg"
                }
  			// ...
            ],
        "skus": {
                	"37": {
                    		"quantity": 8,
                    		"size": "XS"
                	},
                	"38": {
                    		"quantity": 16,
                    		"size": "S"
                	},
                	"39": {
                    		"quantity": 17,
                    		"size": "M"
                	},
            //...
            	}
    },
  {
        "style_id": 2,
        "name": "Desert Brown & Tan",
        "original_price": "140",
        "sale_price": "0",
        "default?": false,
        "photos": [
  			{
                    "thumbnail_url": "urlplaceholder/style_2_photo_number_thumbnail.jpg",
                    "url": "urlplaceholder/style_2_photo_number.jpg"
        }
      // ...
            ],
        "skus": {
                	"37": {
                    		"quantity": 8,
                    		"size": "XS"
                	},
                	"38": {
                    		"quantity": 16,
                    		"size": "S"
                	},
                	"39": {
                    		"quantity": 17,
                    		"size": "M"
                	},
            //...
            	}
    },
  // ...
}
```

Here are helpful links:
1. http://sqlfiddle.com/#!15/70cd7/12
2. http://www.sqlfiddle.com/#!17/9fde6/8




All right, previous example is just one way of doing