json - 对存储在 Postgres 中的 jsonb 数据进行递归查询

Question

我在 postgres 中有一个名为“books”的表，其中有一个名为“j”的 jsonb 列，其中包含以下数据：

{
  "entity": "Book",
  "processes": [
    {  "process": [
        { "processname": "Buy",
          "option": "none",
            "processes": [
              { "process": [
                { "processname": "Order",
                  "option": "none"
                }
                ]
              },
               { "process": [
                { "processname": "Arrive",
                  "option": "none"
                }
                ]
              },
               { "process": [
                { "processname": "Enter",
                  "option": "none"
                }
                ]
              }

             ]
        }
     ]
    },
    { "process": [
        { "processname": "Use",
          "option": "none",
    "processes": [
      { "process": [
          { "processname": "Lend",
            "option": "*",
"processes": [
    {
      "process": [
        {
          "processname": "Borrow",
          "option": "none"
        }
      ]
    },
    {
      "process": [
        {
          "processname": "Return",
          "option": "none"
        }
      ]
    }
 ]
          }
       ]
       } 
      ]
         }
]
    },
    {  "process": [
        { "processname": "Scrap",
          "option": "none",
           "processes": [
              { "process": [
                { "processname": "Sold",
                  "option": "0"
                }
                ]
              },
               { "process": [
                { "processname": "Scrap",
                  "option": "0"
                }
                ]
              }
        ]
    }
  ]
}
]
}

我想检索所有进程名以及他们父母的进程名（即到达他们的路径）。

到目前为止，我有以下代码，但联合部分不起作用。

with recursive jsonrecursion as
(
      select process -> 'processname' as processname, '{}'::int[] as superior, 0 as lv
        from books, jsonb_array_elements(j-> 'processes') processes, jsonb_array_elements(processes-> 'process') process
       where process -> 'processes' is NULL

         union all

      select process -> 'processname' as processname, superior ||  process -> 'processname', lv+1
        from    books, jsonrecursion
       where not process -> 'processname' = any(superior)
)

SELECT processname, superior, lv
FROM jsonrecursion;

有人可以帮忙吗？谢谢

标签： jsonpostgresqlrecursionnested