Multiple layers of nested json Join tables without using join method for faster retrieval time (PostgreSQL)

ehdtlaos

Edrick Ee

Posted on August 16, 2021

Multiple layers of nested json Join tables without using join method for faster retrieval time (PostgreSQL)

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
Enter fullscreen mode Exit fullscreen mode

'default?', (default_style::int::bool) can be used if type is integer instead of boolean

💖 💪 🙅 🚩
ehdtlaos
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