Instead of using JOIN method, write your nested query directly inside of nested query using () !!!
It is much faster than JOIN method query !!!
Mind blowing
SELECT product_id, json_agg(json_build_object(
'style_id', id,
'name', name,
'original_price', original_price,
'sale_price', sale_price,
'default?', default_style,
'photos',
// use your json_agg inside of another json_agg with parentheses!
(SELECT json_agg(json_build_object(
'thumbnail_url', thumbnail_id,
'url', url
)) FROM photos WHERE style_id = styles.id),
'skus',
// Object in object can be also used using parentheses!!
(SELECT
json_object_agg(id,
json_build_object(
'size', size,
'quantity', quantity
)
) as skus
FROM skus
WHERE style_id = styles.id
GROUP by style_id)
)) as results FROM styles
WHERE styles.product_id = 1
GROUP BY product_id
'default?', (default_style::int::bool) can be used if type is integer instead of boolean
Top comments (0)