Question 89771 in PostgreSQL Question posted in
The official documentation can be found here.

How to update each json object in json array? – Postgresql

I have simple table table_b

id (integer) data (json) text (text)
1 {} yes
2 {} no

Json look like

{"types": [{"key": "first_event", "value": false}, {"key": "second_event", "value": false}, {"key": "third_event", "value": false}...]}

I just want to modify data and add to each json object in array ["test1", "test2"] to look like this :

{"types": [{"key": "first_event", "value": false, "can":["test1", "test2"] }, {"key": "second_event", "value": false , "can":["test1", "test2"]}, {"key": "third_event", "value": false , "can":["test1", "test2"]}...]}

ofcourse only where text like "yes"
I have tryed :

UPDATE table_b
SET data = jsonb_set(data , '{types,can}', '["test1", "test2"]'::jsonb, true)
where text like 'yes';

But it does not work. How can i loop over table_b and over data column?

Tags:

2

Answers


  1. 0

    The array value of a JSON object can be modified. It can be simply done by modifying the value present at a given index.

    Login or Signup to reply.
  2. 0

    Using this sample data

    create table table_b as
select * from (values
(1,'{"types": [{"key": "first_event", "value": false}, {"key": "second_event", "value": false}, {"key": "third_event", "value": false}]}'::jsonb,'yes'),
(2,'{}'::jsonb,'no'),
(3,'{"types": [{"key": "first_event", "value": false}]}'::jsonb,'yes')
) table_b(id,data,txt)

    This query updates each array element, keeping the order in the ORDINALITY column

    with table_b2 as (
select 
  id,item, index,
  jsonb_set(item,('{"can"}')::TEXT[],'["test1", "test2"]') new_item
from table_b  cross join 
jsonb_array_elements(data -> 'types') WITH ORDINALITY arr(item, index) 
where txt = 'yes')
select * from table_b2
|
id|item                                   |index|new_item                                                          |
--+---------------------------------------+-----+------------------------------------------------------------------+
 1|{"key": "first_event", "value": false} |    1|{"can": ["test1", "test2"], "key": "first_event", "value": false} |
 1|{"key": "second_event", "value": false}|    2|{"can": ["test1", "test2"], "key": "second_event", "value": false}|
 1|{"key": "third_event", "value": false} |    3|{"can": ["test1", "test2"], "key": "third_event", "value": false} |
 3|{"key": "first_event", "value": false} |    1|{"can": ["test1", "test2"], "key": "first_event", "value": false} |

    The next query concatenates the updated elements back to the array (keeping the right order) and performs a simple update

    with table_b2 as (
select 
  id,item, index,
  ('{types,' || index - 1 || ',"can"}')::TEXT[] AS path,
  jsonb_set(item,('{"can"}')::TEXT[],'["test1", "test2"]') new_item
from table_b  cross join 
jsonb_array_elements(data -> 'types') WITH ORDINALITY arr(item, index) 
where txt = 'yes'),
table_b3 as (
select 
 id,
 jsonb_agg(new_item order by index) new_data
from table_b2
group by id)
update table_b t
set data = table_b3.new_data
from table_b3
where t.id = table_b3.id

select * from table_b order by id;

id|data                                                                                                                                                                                                      |txt|
--+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---+
 1|[{"can": ["test1", "test2"], "key": "first_event", "value": false}, {"can": ["test1", "test2"], "key": "second_event", "value": false}, {"can": ["test1", "test2"], "key": "third_event", "value": false}]|yes|
 2|{}                                                                                                                                                                                                        |no |
 3|[{"can": ["test1", "test2"], "key": "first_event", "value": false}]                                                                                                                                       |yes|
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search