How to transform a JSON array nested inside an object inside another array in Postgres?

Question

I'm using Postgres 9.6 and have a JSON field called credits with the following structure; A list of credits, each with a position and multiple people that can be in that position.

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

I need to transform the nested people array, which are currently just strings representing their names, into objects that have a name and image_url field, like this

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

So far I've only been able to find decent examples of doing this on either the parent JSON array or on an array field nested inside a single JSON object.

So far this is all I've been able to manage and even it is mangling the result.

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

Soviut · Accepted Answer · 2018-10-01 11:54:54Z

3

Create an auxiliary function to simplify the rather complex operation:

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)  
$$;

With the function the update is not so horrible:

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.

edited Oct 1, 2018 at 11:54

Soviut

92.2k53 gold badges210 silver badges285 bronze badges

answered Oct 1, 2018 at 5:57

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Soviut Over a year ago

Using a function is so much more elegant than the multi-nested select aggregations I was attempting.

Collectives™ on Stack Overflow

How to transform a JSON array nested inside an object inside another array in Postgres?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related