Make recursive query from jsonb with tree structure-CodePudding

I have a postgresql table with an unique column root of type jsonb with an unique row:

{
  "code": "1",
  "name": "1",
  "parent": null,
  "children": [
    {
      "code": "2",
      "name": "2",
      "parent": "1",
      "children": [
        {
          "code": "3",
          "name": "3",
          "parent": "2",
          "children": [
            {
              "code": "4",
              "name": "4",
              "parent": "3",
              "children": []
            }
          ]
        },
        {
          "code": "5",
          "name": "5",
          "parent": "2",
          "children": []
        }
      ]
    }
  ]
}

I want to execute a query to return a flat list of all children of a given node at any level. More precisely, given for instance the node with code=2, it should:

find the node in the chain which has code=2 beginning from the top
select its children array
flat the result in order to have a plain list of nodes looking ad children recursively
return them as rows with columns code and name

So the result in this example should be:

CODE NAME
3 3
4 4
5 5

Is that possible?

Postgresql: v14, so I can use the new json syntax

CodePudding user response：

You can build that using the regular "with recursive" expression. There is nothing special about JSON. Here is my take:

with recursive
source as (select '{
  "code": "1",
  "name": "1",
  "parent": null,
  "children": [
    {
      "code": "2",
      "name": "2",
      "parent": "1",
      "children": [
        {
          "code": "3",
          "name": "3",
          "parent": "2",
          "children": [
            {
              "code": "4",
              "name": "4",
              "parent": "3",
              "children": []
            }
          ]
        },
        {
          "code": "5",
          "name": "5",
          "parent": "2",
          "children": []
        }
      ]
    }
  ]
}'::jsonb as root),
nodes as (
    select '{}'::varchar[] as path, code, name, children
    from source, lateral jsonb_to_record(root) as child(code varchar, name varchar, children jsonb)
  union all
    select array_append(nodes.path, nodes.code) as path, child.code, child.name, child.children
    from nodes, lateral jsonb_to_recordset(nodes.children) as child(code varchar, name varchar, children jsonb)
)
select path, code, name from nodes
where path @> array['2'::varchar]