Multiple layers of nested json Join tables without using join method for faster retrieval time (PostgreSQL)
Edrick Ee
Posted on August 16, 2021
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
💖 💪 🙅 🚩
Edrick Ee
Posted on August 16, 2021
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
githubcopilot AI Innovations at Microsoft Ignite 2024 What You Need to Know (Part 2)
November 29, 2024