Update data of array json

Question

MyTable

id |  data
___________
1  |[{"Session1": "", "DeviceId1": ""}, {"Session2": "", "DeviceId2": ""}]

I want update data and set Session1 equal to xxx and DevicceId1 eaqual to yyy

I write this query but this not worked

update MyTable data=jsonb_set(data, '{Session1}', 'xxx',true)

How can update value of array of json in PostgreSQL?

ashkufaraz · Accepted Answer · 2017-02-24 20:10:11Z

1

data is a json array, so the path to Session1 needs to be {0,Session1}, similarly {0,DeviceId1} for DeviceId1

Which would make the update statement:

UPDATE "MyTable"
SET "data" = jsonb_set(jsonb_set(data, '{0,Session1}', '"xxx"', true), '{0,DeviceId1}', '"yyy"', true)
WHERE id = 1

edited Feb 24, 2017 at 20:10

ashkufaraz

5,2997 gold badges54 silver badges85 bronze badges

answered Feb 24, 2017 at 19:44

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

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

2 Comments

ashkufaraz Over a year ago

thank you for answer.if i want select from mytable where data->>session1='xxx' and data->deviceid='yyy' how should write this?

Haleemur Ali Over a year ago

something like this: select * from t where data#>>'{0,Session1}'='xxx' and data#>>'{0,DeviceId1}'='yyy'

klin · Accepted Answer · 2017-02-24 19:42:07Z

1

You can use a json array index (starting from 0) as a path:

update my_table
set data = jsonb_set(data, '{0}', '{"Session1": "xxx", "DeviceId1": "yyy"}')
where id = 1
returning *;

 id |                                     data                                     
----+------------------------------------------------------------------------------
  1 | [{"Session1": "xxx", "DeviceId1": "yyy"}, {"Session2": "", "DeviceId2": ""}]
(1 row)

answered Feb 24, 2017 at 19:42

klin

123k15 gold badges241 silver badges263 bronze badges

Collectives™ on Stack Overflow

Update data of array json

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related