Add LIMIT or ORDER BY inside json_arrayagg()

Question

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?

Answer 1

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:

• Create JSON object from recursive tree structure
• Return multiple columns of the same row as JSON array of objects

For a dynamic number of nested levels:

• How to turn a set of flat trees into a single tree with multiple leaves?

Answer 2

You can perform the ORDER BY within the json_arrayagg, but you will need to do the LIMIT operation within the query on level_two_table by computing a row number for each fk_level_one_id and then using a WHERE clause at the top-level to only select the first n rows for each group.

SELECT jsonb_pretty(
          json_build_object(
                'level_one_table',
                 json_build_object(
                     'id', t0.id,
                     'level_two_table',
                      json_arrayagg(
                        json_build_object(
                          'id', t1.id,
                          'column2', t1.column2,
                          'level_three_table',
                          json_build_object(
                            'id', t2.id,
                            'column3', t2.column3
                          )
                        )
                        ORDER BY t1.column2 DESC
                     )
                 )
          )::jsonb
       ) as json_obj
FROM level_one_table t0 
LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY fk_level_one_id ORDER BY column2 DESC) AS rn FROM 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
WHERE t1.rn IS NULL OR t1.rn <= 2
GROUP BY t0.id

I've expanded the demo from the answer to your previous question to demonstrate this in operation here.