代码之家 › 专栏 › 技术社区 › Soviut

如何在Postgres中转换嵌套在另一个数组中的对象中的JSON数组?

jsonb postgresql json arrays

Soviut · 技术社区 · 7 年前

我使用的是postgres9.6,有一个名为 credits 具有以下结构;一个学分列表,每个学分都有一个职位和多个可以担任该职位的人。

[
  {
    "position": "Set Designers",
    people: [
      "Joe Blow",
      "Tom Thumb"
    ]
  }
]

我需要转换嵌套的 people name 和 image_url 菲尔德,像这样

[
  {
    "position": "Set Designers",
    people: [
      { "name": "Joe Blow", "image_url": "" },
      { "name": "Tom Thumb", "image_url": "" }
    ]
  }
]

到目前为止,我只能在父JSON数组或嵌套在单个JSON对象中的数组字段上找到这样做的好例子。

到目前为止,这是我所能处理的全部,甚至它正在破坏结果。

UPDATE campaigns
  SET credits = (
    SELECT jsonb_build_array(el)
    FROM jsonb_array_elements(credits::jsonb) AS el
  )::jsonb
;

1 回复 | 直到 7 年前

Soviut 7 年前

创建一个辅助函数来简化相当复杂的操作:

create or replace function transform_my_array(arr jsonb)
returns jsonb language sql as $$
    select case when coalesce(arr, '[]') = '[]' then '[]'
    else jsonb_agg(jsonb_build_object('name', value, 'image_url', '')) end
    from jsonb_array_elements(arr)  
$$;

update campaigns
  set credits = (
    select jsonb_agg(jsonb_set(el, '{people}', transform_my_array(el->'people')))
    from jsonb_array_elements(credits::jsonb) as el
  )::jsonb
;

Working example in rextester.