Search code examples
sqljsondatabasepostgresqlpostgresql-16

Add LIMIT or ORDER BY inside json_arrayagg()

I have tables level_one_table, level_two_table, and level_three_table.
level_one_table to level_two_table: one to multiple rows
level_two_table to level_three_table: one to one row

Goal: select them all and level_two_table rows able to use LIMIT and ORDER BY. Return data structure like below:

{
  "level_one_table": {
     "id": ..,
     "... all other field in level_one_table"
     
    "level_two_table": [{
       "id": ..,
       "... all other field in level_two_table",
       
       "level_three_table": {
          "id": ..,
          "... all other field in level_three_table"
       }
    }]
  }
}

level_one_table:

"id" ...
1 ...
2 ...

level_two_table:

"id" "fk_level_one_id" ...
1 1 ...
2 1 ...

level_three_table:

"id" "fk_level_two_id" ...
1 1 ...
2 2 ... 
SELECT  
  json_build_object(
    'level_one_table', json_build_object(
      'id', t0.id,
      'level_two_table', json_arrayagg(json_build_object(
        'id', t1.id,
        // ... other t1 columns
        // ORDER BY t1.column DESC LIMIT 5
        'level_three_table', json_build_object(
          'id', t2.id
          // ... other t2 columns
        )
      ))
    )
  ) 
FROM level_one_table t0 
LEFT JOIN level_two_table t1 ON t0.id = t1.fk_level_one_id 
LEFT JOIN level_three_table t2 ON t1.id = t2.fk_level_two_id 
GROUP BY t0.id
// ORDER BY t0.column ...  DESC LIMIT 10

Is there a way to add LIMIT or ORDER BY inside json_arrayagg()?
Or any other solution get the same result?

Solution

  • Do the aggregation in a LATERAL subquery. Then you can add plain ORDER BY & LIMIT before you aggregate:

    SELECT json_build_object('id', t1.id /* other t1 cols */
                      , 'level_two_table', level_two_table) AS result
FROM   level_one_table t1 
CROSS  JOIN LATERAL (
   SELECT json_agg(sub) AS level_two_table
   FROM  (
      SELECT t2.id /* other t2 cols */
           , json_build_object('id', t3.id /* other t3 cols */) AS level_three_table
      FROM   level_two_table        t2
      LEFT   JOIN level_three_table t3 ON t3.fk_level_two_id = t2.id
      WHERE  t2.fk_level_one_id = t1.id
      ORDER  BY t2.col DESC
      LIMIT  5
      ) sub
   ) sub;

    fiddle

    Notably, I synced table aliases with table names (table names 1-based, but aliases 0-based wasn't helpful).

    Here, json_agg() can replace the more sophisticated json_arrayagg() (added in Postgres 16) to aggregate the JSON array.

    Result:

    '{
    "id": 1,
    "level_two_table": [
        {"id": 1, "level_three_table": {"id": 1}},
        {"id": 2, "level_three_table": {"id": 2}}
    ]
}'
'{
    "id": 2,
    "level_two_table": null
}'

    You get a dummy entry "level_two_table": null if there are no related rows. Strip all null values with json_strip_nulls(), or use CASE or similar to strip electively.

    Related:

    For a dynamic number of nested levels: