Get path to nested JSON element

Hello everyone,
Consider we have a JSONB column with array of complex JSON objects, e.g.

id | attributes_col
----+--------------
xyz1, | [
  {"id": "abc", "attrs": [{"id": "attr1", "value": "xxx"}, {"id": "attr2", "value": "yyy", ...}]},
  ...
]

And we would like to update some of the nested elements. It’s no problem to do so by hand:

UPDATE attributes_tables SET attributes_col=json_set(attributes_col, '{0,attrs,0,value}', '"zzz"'::jsonb) where id=xyz1;

But there is a problem: how to get that json path dynamically like in postgres? example: How to update objects inside JSONB arrays with PostgreSQL

Any ideas and help are appreciated :slight_smile:

Hi @x4t3a,

I believe you are referring to the following query from the referenced blog post:

with contact_email as (
  select ('{'||index-1||',value}')::text[] as path
    from customers
        ,jsonb_array_elements(contacts) with ordinality arr(contact, index)
   where contact->>'type' = 'email'
     and name = 'Jimi'
)
update customers
   set contacts = jsonb_set(contacts, contact_email.path, '"jimi.hendrix@gmail.com"', false)
  from contact_email
 where name = 'Jimi';

Is this correct? If so, then it seems like the reason why this query does not parse in CockroachDB is because it is using the reserved keyword index, which is treated slightly differently by CockroachDB’s parser than it is in Postgres.

The solution seems to be to use a different identifier. For instance, the following query works for me:

with contact_email as (
  select ('{'||idx-1||',value}')::text[] as path
    from customers
        ,jsonb_array_elements(contacts) with ordinality arr(contact, idx)
   where contact->>'type' = 'email'
     and name = 'Jimi'
)
update customers
   set contacts = jsonb_set(contacts, contact_email.path, '"jimi.hendrix@gmail.com"', false)
  from contact_email
 where name = 'Jimi';

Notice that the only change is switching index with idx.

Did I understand the question correctly? Does this help?

Nathan

1 Like

@nathan thank you very much! :slight_smile: