plpgsql jsonb_set for JSON array of objects with nested arrays

Question

Using PostgreSQL 14.0 PL/SQL (inside a do block). Attempting to:

query a certain key ('county') in a jsonb array of objects (which in turn has object + nested arrays) based on dynamic variable value (named cty.cty_name)
retrieve value and change it
update said jsonb to reflect the updated value in (2)
after executing (3) on multiple values, create new table with above jsonb as a row with one column

steps (1) and (2) execute properly. But, for the life of me, I can't figure (3) out.

jsonb object(res) -- may have 100s of array items at index root:

[ {"county": "x", "dpa": ["a", "b", "c"]},
 {"county": "y", "dpa": ["d", "e", "f"]},
 {"county": "z", "dpa": ["h", "i", "j"]},
 ...
]

code for (1) and (2) above:

execute format('select jsonb_path_query_array(''%s'', ''$[*]?(@.%s=="%s")'')',
            res,'county',cty.cty_name) into s1;
execute format('select jsonb_array_elements(''%s'')->''%s''', s1,'dpa') into s2;
s2 := s2 || jsonb_build_array(r1.name);

where say:

cty.cty_name is y (which is created from a select in for loop)
r1.name is m

s2 holds the new value e.g. ["d", "e", "f", "m"]

Now, to execute (3) I need path to dpa for which key county matches value y in some index in res. Having tried (and failed miserably) at various permutations of jsonb_query_path with SQL/JSON Path, dollar-quoted strings, jsonb_path_to_array with double-quoted hell for format queries, other SO solutions which use idx or idx-1 (but I don't have JSON in table), I had to resort to soliciting the Borg collective's wisdom. Help please.

haha.. @LaurenzAlbe I despise JSON. However, the output is supposed to be an input to an interwebs front-end; which meant JSON was the easiest design choice. — Morik-san
– Morik-san, Commented Nov 24, 2021 at 14:43

JJ Ward · Accepted Answer · 2022-01-05 11:06:04Z

1

The problem you're running into with the current approach is twofold:

there's no way to "delete" the matching county object in vitro (via jsonb_set(), etc.)
there's no way to force uniqueness (to utilize the ON CONFLICT ... DO UPDATE mechanism) within the json document itself to accomplish the same

When we get to

Now, to execute (3) I need path to dpa for which key county matches value y in some index in res.

instead of updating the existing record in-place, why not just remove the matching record (with now-stale value for "dpa"), re-aggregate what remains (i.e. the non-matching objects), and then append the updated matching object to the aggregated jsonb array, similar to:

SELECT jsonb_agg(a) || jsonb_build_object('county', 'y', 'dpa', (jsonb_path_query_array(res, '$[*] ? (@.county=="y")')#>'{0,dpa}') || jsonb_build_array('m') )
FROM jsonb_array_elements(res) a
WHERE NOT a @> (jsonb_path_query_array(res, '$[*] ? (@.county=="y")')->0);

This gives a single jsonb value back per your specification in (4); you should be able to parameterize this into your EXECUTE invocation as necessary.

Worth noting on the output order, if you're looping over the initial "res" array, then the order of the objects within the array (with respect to the "county" values of the driving cursor) will be restored according to the order of the cursor you're iterating through for "county".

This is because a full cycle through said cursor will remove each of the old objects and replace them at the end of the resultant array, so defining an ORDER BY clause in this cursor will be important if this is relevant.

answered Jan 5, 2022 at 11:06

JJ Ward

14210 bronze badges

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

1 Comment

Morik-san Over a year ago

truely appreciate the thoughtful and thorough response. I'll review and implement the recommended changes and report back the results upon return over the next few days (still refusing to start 2022) :-)

Collectives™ on Stack Overflow

plpgsql jsonb_set for JSON array of objects with nested arrays

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