PostgreSQL: Updating attributes of elements in nested arrays within JSONB structure

Question

I have a jsonb structure in PostgreSQL 9.6 that contains a nested array structure similar to the example below:

continents:[
   {
       id: 1,
       name: 'North America',
       countries: [
           {
               id: 1,
               name: 'USA',
               subdivision: [
                  {
                     id: 1,
                     name: 'Oregon',
                     type: 'SOME_TYPE'
                  }
               ]
           } 
       ]
   }
]

How can I change the 'type' attribute of multiple subdivisions since it its nested within two arrays ( countries and subdivision )?

I've come across other answers and am able to do so on a record by record basis (assuming table is map and jsonb column is divisions ):

update map
set divisions = jsonb_set( divisions, '{continents,0,countries,0,subdivisions,0,type}', '"STATE"', FALSE);

Is there a way to programatically change that attribute for all subdivisions?

I think I'm getting close, I can query all subdivision types using the query below, but am struggling to figure out how to update them:

WITH subdivision_data AS (
    WITH country_data AS (
       select continents -> 'countries' as countries
       from  map, jsonb_array_elements( map.divisions -> 'continents' ) continents
    )
    select country_item -> 'subdivisions' as subdivisions
    from country_data cd, jsonb_array_elements( cd.countries ) country_item
)
select subdivision_item ->> 'type' as subdivision_type
from subdivision_data sub, jsonb_array_elements( sub.subdivisions ) subdivision_item;

Here's some of the questions I came across. They seem to work only if you are trying to update a single level array though:

postgresql 9.5 using jsonb_set for updating specific jsonb array value

How to update deeply nested JSON object based on filter criteria in Postgres?

Postgres/JSON - update all array elements

speed is not much of a concern since I don't intend on updating this structure often. — Max

Roman Pekar Roman Pekar · Accepted Answer · 2019-04-20T11:47:47

At first I thought that something like this would work:

update map as m set
    divisions = jsonb_set(m1.divisions, array['continents',(d.rn-1)::text,'countries',(c.rn-1)::text,'subdivisions',(s.rn-1)::text,'type'], '"STATE"', FALSE)
from map as m1,
    jsonb_array_elements(m1.divisions -> 'continents') with ordinality as d(data,rn),
    jsonb_array_elements(d.data -> 'countries') with ordinality as c(data,rn),
    jsonb_array_elements(c.data -> 'subdivisions') with ordinality as s(data,rn)
where
    m1.id = m.id

db<>fiddle demo

But this does't work - see documentation

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

What you can do is unnest your jsons with functions-json and then aggregate them back:

update map set
    divisions = jsonb_set(divisions, array['continents'],
        (select
            jsonb_agg(jsonb_set(
                d, array['countries'],
                (select 
                    jsonb_agg(jsonb_set(
                        c, array['subdivisions'],
                        (select
                            jsonb_agg(jsonb_set(s, array['type'], '"STATE"', FALSE))
                        from jsonb_array_elements(c -> 'subdivisions') as s),
                        FALSE
                    ))
                from jsonb_array_elements(d -> 'countries') as c)
            ))
        from jsonb_array_elements(divisions -> 'continents') as d),
        FALSE
    )

db<>fiddle demo

You can also create helper function which you can use instead of multiple subqueries:

create function jsonb_update_path(_data jsonb, _path text[], _value jsonb)
returns jsonb
as $$
begin
    if array_length(_path, 1) = 1 then
        return jsonb_set(_data, _path, _value, FALSE);
    else
        return (
            jsonb_set(
                _data, _path[1:1],
                (
                    select
                        jsonb_agg(jsonb_update_path(e, _path[2:], _value))
                    from jsonb_array_elements(_data -> _path[1]) as e
                )
            )
        );
    end if;
end
$$
language plpgsql

update map set
    divisions = jsonb_update_path(divisions, '{continents,countries,subdivisions,type}', '"STATE"')

db<>fiddle demo

PostgreSQL: Updating attributes of elements in nested arrays within JSONB structure

2 Answers