The PostgreSQL database in jsonb stores an object of type

{ strips: [ {id: 1, key: 'value'} ] }

How to change the value, knowing the id? It is desirable one transaction.

  • Something like update table set field->'value' = @value where field->'id' = @id - Akina

1 answer 1

postgresql has a list of json processing functions , but sometimes it is a bit lacking. The task can be solved, but sometimes you need to think about whether it is worth solving it exactly and whether it is better to do it in some applied language ... However, to the point.

Suppose we are talking about a table tablename and a jsonb field in it data . And we want to replace the key with the newvalue the element with id 1. First, we write select , so as not to touch the data and we could compare the changes:

 select data, jsonb_set(data, '{strips}', ( select jsonb_agg(case when (el->>'id')::int = 1 then el || jsonb_build_object('key', 'newvalue') else el end) from jsonb_array_elements(data->'strips') as el) ) from tablename where data ? 'strips';

The trouble is that you have an array of records in strips . Therefore, searching and changing is done by expanding the array into a list of strings and then assembling it back.

And the array also prevents to write an effective where condition for selecting strings. How this can be perverted - perhaps the topic of a separate issue.

We rewrite to update :

 update tablename set data = jsonb_set(data, '{strips}', ( select jsonb_agg(case when (el->>'id')::int = 1 then el || jsonb_build_object('key', 'newvalue') else el end) from jsonb_array_elements(data->'strips') as el) ) where data ? 'strips';