How to update postgresql using json sub-element

Question

How can I update "A" named value with 10.2 where ID equal 1003 in to a postgresql database table.

Json Data Table

Id	Column
1001	{"results":[{"name":"A","value":"7.8"}, {"name":"B","value":"0.5"}]}
1002	{"results":[{"name":"B","value":"5.4"}, {"name":"D","value":"4.5"}]}
1003	{"results":[{"name":"D","value":"4.8"}, {"name":"A","value":"6.7"}]}

Result after update

Id	Column
1001	{"results":[{"name":"A","value":"7.8"}, {"name":"B","value":"0.5"}]}
1002	{"results":[{"name":"B","value":"5.4"}, {"name":"D","value":"4.5"}]}
1003	{"results":[{"name":"D","value":"4.8"}, {"name":"A","value":"10.2"}]}

var211 · Accepted Answer · 2022-05-11 09:43:23Z

1

It isn't a simple query, was able to make it with CTE only. I refer to your example table as test:

with item_in_list_pos as (
    select
        pos - 1 as pos
        from test, jsonb_array_elements(column1->'results') with ordinality a(elem, pos)
        where (
            id = 1003
            and elem->>'name' = 'A'
        )
)
update test
    set 
        column1 = jsonb_set(column1, array['results', pos, 'value']::text[], to_jsonb('10.2'::text))
    from item_in_list_pos
    where (
        id = 1003
    )

edited May 11, 2022 at 9:43

answered May 11, 2022 at 7:50

var211

6066 silver badges10 bronze badges

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

10 Comments

Satish Over a year ago

getting error, kindly pls go through below link dbfiddle.uk/…

var211 Over a year ago

Sorry didn't know "Column" was jsonb type, need only to switch to jsonb_array_elements, I will update query.

var211 Over a year ago

All is good now.

var211 Over a year ago

Optimized query a little

var211 Over a year ago

You can add them, it is a version for your DB fiddle: dbfiddle.uk/…

|

Collectives™ on Stack Overflow

How to update postgresql using json sub-element

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related