Postgresql 和 jsonb - 将键/值插入多级数组

非常类似于这个帖子 https://stackoverflow.com/questions/58959678/postgresql-add-key-to-each-objects-of-an-jsonb-array，但我很难适应他们的解决方案..

我的桌子：public.challenge， 柱子lines JSONB

我的初始 JSON 在lines :

[
  {
    "line": 1,
    "blocs": [
      {
        "size": 100,
        "name": "abc"
      },
      {
        "size": 100,
        "name": "def"
      },
      {
        "size": 100,
        "name": "ghi"
      }
    ]
  },
  {
    "line": 2,
    "blocs": [
      {
        "size": 100,
        "name": "xyz"
      }
    ]
  }
]

所需更新：

[
  {
    "line": 1,
    "blocs": [
      {
        "size": 100,
        "name": "abc",
        "type": "regular"
      },
      {
        "size": 100,
        "name": "def",
        "type": "regular"
      },
      {
        "size": 100,
        "name": "ghi",
        "type": "regular"
      }
    ]
  },
  {
    "line": 2,
    "blocs": [
      {
        "size": 100,
        "name": "xyz",
        "type": "regular"
      }
    ]
  }
]

所以基本上我需要添加type每个对象中的键+值blocs，对于根数组的每个元素。

我不成功的尝试如下所示：

UPDATE public.challenge SET lines = jsonb_set(lines, '{}', (

    SELECT jsonb_set(line, '{blocs}', (
        SELECT jsonb_agg( bloc || '{"type":"regular"}' )
        FROM jsonb_array_elements(line->'{blocs}') bloc
    ))

    FROM jsonb_array_elements(lines) line
))
;

（目前它将整个列设置为空，可能是由于jsonb_set(lines, '{}'而我的 json 以数组开头？）

Thanks!

Use jsonb_array_elements取消嵌套所有数组元素，然后添加所需的 json 并使用jsonb_agg再次聚合它：

with cte as
 (select id,
         jsonb_agg(jsonb_set(val1,
                             '{blocs}',
                             (select jsonb_agg(arr2 || '{"type": "regular"}')
                                from jsonb_array_elements(arr1.val1 - >
                                                          'blocs') arr2)))
    from challenge, 
         jsonb_array_elements(lines) arr1(val1)
   group by 1) 
update challenge 
   set lines = (cte.jsonb_agg) 
  from cte 
 where challenge.id = cte.id