How to update postgresql using json sub-element-CodePudding

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"}]}

CodePudding user response：

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
    )