Postgres Recursive Query with Row_To_JSON

Extract JSON values recursivey in postgres

I would use a recursive query, but with jsonb_array_elements():

with recursive cte as (
    select id, info ->> 'fragmentName' as val, info -> 'fragments' as info, 1 lvl 
    from mytable 
    where id = 2
    union all
    select c.id, x.info ->> 'fragmentName', x.info -> 'fragments', c.lvl + 1
    from cte c
    cross join lateral jsonb_array_elements(c.info) as x(info)
    where c.info is not null
)
select id, val, lvl
from cte
where val is not null

The query traverses the object depth-first; at each step of the way, we unnest the json array and check if a fragment name is available. We don't need to check the types of the returned values: we just use the standard functions, until the data exhausts.

Demo on DB Fiddle

Sample data:

{
    "fragments": [
        {
            "fragments": [
                {
                    "fragments": [
                        {
                            "fragments": [
                            ],
                            "fragmentName": "D"
                        },
                        {
                            "fragments": [
                            ],
                            "fragmentName": "E"
                        },
                        {
                            "fragments": [
                            ],
                            "fragmentName": "F"
                        }
                    ],
                    "fragmentName": "C"
                }
            ],
            "fragmentName": "B"
        }
    ],
    "fragmentName": "A"
}

Results:

id | val | lvl
-: | :-- | --:
 2 | A   |   1
 2 | B   |   2
 2 | C   |   3
 2 | D   |   4
 2 | E   |   4
 2 | F   |   4

Recursive JSON generation in PostgreSQL

Let's break it into pieces. First, you will have nested subqueries to create the nested arrays. Common Table Expressions may help.

The other tricks are row_to_json and json_agg.

The first gotcha is that row_to_json needs the table as an argument to return the correct labels.

select json_agg(locations) from locations

Will return a json object for each row. To use only certain fields you will need to either create a type and cast to it or use a CTE and the syntax above. I'd use the CTE in most cases.

So you will end up with 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 on the last line use json_agg(midlevel1) instead row_to_json(midlevel1) to return one array of all rows.

CTEs also support regression with the RECURSIVE modifier. However, that returns one table with the results of the regression and not a nested JSON structure. As a result, you will probably need to explicitly code the desired levels of nesting.

If an element doesn't exist Postgres will return null. For example a list of sub-locations where there are none will return "locations":[null]. To replace that with a more meaningful result the case when <> then <> else '[]' end or if <> then <> else '[]' end can be used. The first is the 'searched case' where each test is a Boolean expression.

Postgres rows to complex json

You need to use a recursive query to follow the recursion of the ids.

SELECT row_to_json(t)
FROM (
  SELECT id, text,
    (
      SELECT array_to_json(array_agg(row_to_json(x))) AS comments 
      FROM (                               
        SELECT id, text, post_id, reply_to,
          ( SELECT array_to_json(array_agg(row_to_json(d)))
            FROM (
                    WITH RECURSIVE temp
                    AS
                    (
                      SELECT id, text, post_id, reply_to
                      FROM comments
                      WHERE reply_to IS NULL
                        AND post_id = posts.id
                        AND id = c.id
                      UNION ALL
                      SELECT nxt.id, nxt.text, nxt.post_id, nxt.reply_to
                      FROM temp prv
                      JOIN comments nxt ON prv.id = nxt.reply_to
                      WHERE nxt.reply_to IS NOT NULL
                     ) SELECT * FROM temp WHERE reply_to IS NOT NULL
                   ) d
           ) as replies
        FROM comments c
        WHERE c.reply_to IS NULL
          AND c.post_id = posts.id
      ) x
    )
  FROM posts
) t

Here is the SQLFiddle.

Semi-formatted output:

{"id":1,
 "text":"Test post",
 "comments":[{
     "id":1,
     "text":"Test comment",
     "post_id":1,
     "reply_to":null,
     "replies":[{
         "id":2,
         "text":"Test reply",
         "post_id":1,
         "reply_to":1
         },
        {"id":3,
         "text":"Reply reply",
         "post_id":1,
         "reply_to":2
        }
     ]},
     {"id":4,
      "text":"Comment 2",
      "post_id":1,
      "reply_to":null,
      "replies":[{
          "id":5,
          "text":"Reply 2",
          "post_id":1,
          "reply_to":4
      }]
    }
]}

That's a doozy.

If you are able to rename reply_to to parent_id it would describe the relationship right in the column name.

PostgreSQL Recursive CTE issue with JOINs inside

You need to return the box, pipeline, plate of m, not t, in your second part of CTE. Otherwise you're going to go through solutions, yes, but not going to display what you're currently on for the rest of the stuff (which is what seems like you want). You'll instead display the first one only.

WITH RECURSIVE parents (box, pipeline, plate, made_solution_id, used_solution_id)
AS (
    --get leaf solution
    SELECT
    box, pipeline, plate, made_solution_id, used_solution_id
    FROM
    main
    JOIN
    mixture
    ON
    solution = made_solution_id

UNION
    --get parent solutions
    SELECT
    m.box, m.pipeline, m.plate, m.made_solution_id, m.used_solution_id
    FROM
    main t
    JOIN
    parents pt
    ON
    pt.made_solution_id = t.solution
    JOIN
    mixture m
    ON
    pt.used_solution_id = m.made_solution_id
)
SELECT * from parents

Postgres JSONB query about nested/recursive elements

UPDATE: Thanks to therealgaxbo on reddit, we started with my original code and developed something more concise:

with recursive deconstruct (jsonlevel) as(
    values ('{"id":1,"children":[{"id":2},{"id":3,"children":[{"id":4}]}]}'::json)

    union all

    select 
        case left(jsonlevel::text, 1)
            when '{' then (json_each(jsonlevel)).value
            when '[' then json_array_elements(jsonlevel)
        end as jsonlevel
    from
        deconstruct
    where
        left(jsonlevel::text, 1) in ('{', '[')
)
select * from deconstruct where case when left(jsonlevel::text, 1) = '{' then jsonlevel->>'id' = '4' else false end;

My original response below:

I experimented like crazy and finally came up with something like this:

with recursive ret(jsondata) as
(select row_to_json(col)::text jsondata from 
json_each('{
   "id":1,
   "children": [
      { "id":2 },
      { "id": 3, "children": [
         { "id": 4 }
         ]
      }
   ]
}'::json) col
union 
select case when left(jsondata::text,1)='[' then row_to_json(json_each(json_array_elements(jsondata)))::text
when left((jsondata->>'value'),2)='{}' then null::text
when left((jsondata->>'value')::text,1)='[' then row_to_json(json_each(json_array_elements(jsondata->'value')))::text
else  ('{"key":'||(jsondata->'key')||', "value":'||(jsondata->'value')||'}')::json::text end jsondata 
from (
select row_to_json(json_each(ret.jsondata::json)) jsondata
from ret) xyz

)
select max(1) from ret
where jsondata::json->>'key'='id' 
and jsondata::json->>'value'='1'

Postgres: is there any row_to_json equivalent that returns values only?

You could create a simple SQL function that converts a row into the desired format:

CREATE FUNCTION row2json(anyelement) RETURNS json
   LANGUAGE sql STABLE AS
'SELECT json_agg(z.value) FROM json_each(row_to_json($1)) z';

Then you use that to transform the output:

SELECT row2json(mytab) FROM mytab;

If performance is more important than JSON output, just cast the result to a string:

SELECT CAST(mytab AS text) FROM mytab;

Timestamp format on row_to_json

There is no way to influence the format used by row_to_json.

You could define a view on your table and use

to_char(row_added_dttm, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"')

to format the timestamp as a string.

Then you can use row_to_json on that view to get your desired result.

Nesting PostgreSQL relational query results as JSON objects

I managed to solve this by simply LEFT JOINing the grandchildren table:

SELECT
  p.*,
  COALESCE(json_agg(json_build_object(
    'child_id', c.child_id,
    'child_name', c.child_name
    -- * GET ALL grandchildren FOR THIS child HERE!
  )) FILTER (WHERE c.child_id IS NOT NULL), '[]')
  AS children
FROM parent p
LEFT JOIN parent_children pc
ON pc.parent_id = p.parent_id
LEFT JOIN children c
ON c.child_id = pc.child_id
LEFT JOIN (
  SELECT
    c.*,
    COALESCE(json_agg(json_build_object(
      'grandchild_id', g.grandchild_id,
      'grandchild_name', g.grandchild_name
    ))
    FILTER (WHERE g.grandchild_id IS NOT NULL), '[]') 
    AS grandchildren
  FROM children c
  LEFT JOIN children_grandchildren cg
  ON c.child_id = cg.child_id
  LEFT JOIN grandchildren g
  ON g.grandchild_id = cg.grandchild_id
  GROUP BY c.child_id
) grandchildren ON grandchildren.child_id = c.child_id
GROUP BY p.parent_id;

This produces the JSON object with relations like I needed it:

[
  {
    "parent_id": 1,
    "parent_column": "whatever",
    "children": [
      {
        "child_id": 1,
        "child_name": "le child",
        "grandchildren": [
          {
            "grandchild_id": 1,
            "grandchild_name": "foo"
          },
          {
            "grandchild_id": 2,
            "grandchild_name": "bar"
          }
        ]
      },
      {
        "child_id": 2,
        "child_name": "le second child",
        "grandchildren": []
      }
    ]
  },
  {
    "parent_id": 2,
    "parent_name": "second",
    "children": []
  }
]

---

Related Topics