Recursive JSON in PostgreSQL

select json_agg(locations) from locations

WITH lowlevel1 AS 
( 
 SELECT a, 
 b, 
 c 
 FROM tab1) ,lowlevel2 AS 
( 
 SELECT b, 
 c, 
 d 
 FROM tab2), midlevel1 AS 
( 
 SELECT e, 
 f, 
 g, 
 json_agg(lowlevel1) AS lab1, 
 j son_agg(lowlevel2) AS lab2 
 FROM tab3 
 LEFT OUTER JOIN lowlevel1 
 ON tab3.id = lowlevel1.parent 
 LEFT OUTER JOIN lowlevel2 
 ON tab3.id = lovlevel2 .parent)
SELECT row_to_json(midlevel1) from midlevel1

I have the following table in PostgreSQL 9.5 server:

The notable structure is that theoretically the position is infinitely recursive. I need to generate a JSON message from the root position and recurse to all sub positions; each position has some attributes, a series of inventory items and a series of sub positions.

How to create a high-performance query for this? I am researching various PostgreSQL JSON functions, LATERAL keywords, CTE, and am a bit confused. I have done JSON output using non-recursive query, but I am not sure how to handle recursion cleanly.

Here is an example Output:

{
 "id": 1000,
 "name": "By Location",
 "type": "SITE ",
 "locations": [
 {
 "id": 1005,
 "name": "Storage A",
 "type": "STOR" ,
 "locations": [...(same schema as parent)... ],
 "inventories": [...(see below for schema)... ]
 },
 {
 "id": 1017,
 "name": "Storage B",
 "name": "COLD",
 "locations": [...(same schema as parent)... ],
 "inventories": [...(see below for schema)... ]
 }
 ],
 "inventories": [
 {
 "id": 5340,
 "product_id": 9120,
 "name": "Product X",
 " thumb": "https://example.com/api/images/nnnn.jpg",
 "sort_order": 1,
 "par_level": 3.5,
 "created": 1452898800 ,
 "updated": 1453071600,
 "measures": [< br /> {"id": 3498, "quantity": 2.25, "created": 1453071600, "updated": 1453071600},
 {"id": 3456, "quantity": 3.25, "created": 1452898800, "updated": 1452898800}
 ]
 }
 ]
}

Let’s divide it into Block. First, you will use nested subqueries to create nested arrays. Common table expressions may help.

Other tricks are row_to_json and json_agg.

The first problem is that row_to_json needs the table as a parameter to return the correct label.

select json_agg(locations) from locations

will return for each row A json object. To use only certain fields, you need to create a type and cast it or use CTE and the above syntax. In most cases, I will use CTE.

So you will eventually Get something like:

WITH lowlevel1 AS 
( 
 SELECT a, 
 b, 
 c 
 FROM tab1) ,lowlevel2 AS 
( 
 SELECT b, 
 c, 
 d 
 FROM tab2) ,midlevel1 AS 
( 
 SELECT e, 
 f, 
 g, 
 json_agg(lowlevel1) AS lab1, 
 json_agg(lowlevel2) AS lab2 
 FROM tab3 
 LEFT OUTER JOIN lowlevel1 
 ON tab3.id = lowlevel1.parent 
 LEFT OUTER JOIN lowlevel2 
 ON tab3.id = lovlevel2.parent)
SELECT row_to_json(midlevel1) from midlevel1

Or use json_agg(midlevel1) instead of row_to_json(midlevel1) in the last line to return an array of all rows.

CTE also supports the use of the RECURSIVE modifier for regression. But , It returns a table containing regression results, not a nested JSON structure. Therefore, you may need to explicitly encode the required nesting level.

If the element does not exist, Postgres will return null. For example, a list of sub-locations that does not have will return “location”: [null]. In order to replace it with a more meaningful result, <> then <> else'[]’ ends or if <> then <> else'[]’ The end can be used. The first one is “search case”, where each test is a boolean expression.