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
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
{
"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:
All right, previous example is just one way of doing
Top comments (2)
This is awesome edrick!
This is awesome edrick~