poStrgesQL：从JSONB值的嵌套数组字段编辑特定字段

Question

表：

table Bar(
  id int,
  my_json jsonb
)

my_json的示例：

{
  "foo": "bar",
  "columns": [
    {
      "type": "integer",
      "field": "myField",
      "children": [
        {
          "type": "integer",
          "field": "myField"
        }
      ]
    },
    {
      "type": "integer",
      "field": "otherField",
      "children": [
        {
          "type": "integer",
          "field": "myField"
        }
      ]
    },
    {
      "type": "integer",
      "field": "baz",
      "children": null
    }
  ]
}

我需要更改所有“ type”：“ integer” “ type” type“：“ string” 匹配<代码>“字段”等于“ myfield”。

我使用此下一个SQL查询制作了LVL 1（在列 lvl）：

with query as (
    select ('{columns,' || index - 1 || ',type}')::text[] as path,
           b.id
    from Bar b,
         jsonb_array_elements(my_json -> 'columns') with ordinality arr(col, index)
    where col ->> 'field' = 'myField'
      and col ->> 'type' = 'integer'
)
update Bar b
set my_json = jsonb_set(b.my_json, query.path, '"string"', false)
from query
where b.id = query.id;

我必须使用Nested Array 可以实现LVL 2（理想地了解任何LVL）保持无效，我不知道该怎么做。

Answer 1

我做到了！这是我发现的解决方案：

with non_null_children as (
    select index - 1 as idx,
           col,
           b.id as bar_id
    from Bar b,
         jsonb_array_elements(my_json -> 'columns') with ordinality arr(col, index)
    where col ->> 'children' is not null
), query as (
    select ('{columns,' || nnc.idx || ',children,' || index - 1 || ',type}')::text[] as path,
           b.id
    from Bar b,
         non_null_children nnc,
         jsonb_array_elements(nnc.col -> 'children') with ordinality arr(col2, index)
    where b.id = nnc.bar_id
      and col2 ->> 'field' = 'myField'
      and col2 ->> 'type' = 'integer'
)
update Bar b
set my_json = jsonb_set(b.my_json, query.path, '"string"', false)
from query
where b.id = query.id;

说明：

• 我列出了所有包含孩子的项目，每个项目与列索引及其bar ID - ＆gt; non_null_children < / code>
• 对于每个bar ID /列索引，我可以像LVL 1一样在匹配的现场操作上工作。

我不知道这是否是最优化的解决方案，如果您认为这是必要的。