Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table Test_Table (clusterId INT, owner VARCHAR(50), data JSONB);
insert into Test_Table
values
(1, 'Jacky', '{"certified":[{"Type":"New Instance","Status":"Active","created":"2020-02-03","id_no":"f6b2197c4889"},{"Type":"New Instance","Status":"Failed","created":"2020-02-04","id_no":"216430036272"},{"Type":"New Instance","Status":"Active","created":"2020-02-04","id_no":"3bb515f781c6"},{"Type":"New Instance","Status":"Active","created":"2020-02-04","id_no":"26ae740e9b21"},{"Type":"New Instance","Status":"Active","created":"2020-02-04","id_no":"7e30a80211a2"}],"in_common":[{"name":"Kodali","owner":"Jet"},{"name":"Lakers","owner":"Fantastic"}]}
' ),(2, 'Bob', NULL), (3, 'Wolf', NULL);
3 rows affected

select * from test_table;


clusterid
owner
data


1
Jacky
{"certified": [{"Type": "New Instance", "id_no": "f6b2197c4889", "Status": "Active", "created": "2020-02-03"}, {"Type": "New Instance", "id_no": "216430036272", "Status": "Failed", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "3bb515f781c6", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "26ae740e9b21", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "7e30a80211a2", "Status": "Active", "created": "2020-02-04"}], "in_common": [{"name": "Kodali", "owner": "Jet"}, {"name": "Lakers", "owner": "Fantastic"}]}

2
Bob
null

3
Wolf
null


select 
    data->>'certified' @@ '$.id_no =  "216430036272"',
    data->>'certified',
    jsonb_set(data,'{certified}','"foo"')
from test_table
where clusterid = 1;


?column?
?column?
jsonb_set


t
[{"Type": "New Instance", "id_no": "f6b2197c4889", "Status": "Active", "created": "2020-02-03"}, {"Type": "New Instance", "id_no": "216430036272", "Status": "Failed", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "3bb515f781c6", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "26ae740e9b21", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "7e30a80211a2", "Status": "Active", "created": "2020-02-04"}]
{"certified": "foo", "in_common": [{"name": "Kodali", "owner": "Jet"}, {"name": "Lakers", "owner": "Fantastic"}]}


with js as (
    select 
        jsonb_array_elements((data->>'certified')::jsonb) as elems
    from test_table
    where clusterid = 1
), to_upd as (
    select
        --elems->>'Type' as "type",
        --elems->>'id_no' as id_no,
        --elems->>'Status' as status,
        --elems->>'created' as created,
        jsonb_set(elems,'{Status}','"Active"') as elems
    from js
    where js.elems->>'id_no' = '216430036272'
), to_keep as (
    select elems
    from js
    where js.elems->>'id_no' != '216430036272'
), final_array as (
    select jsonb_agg(elems) as elems
    from (
        select elems from to_keep
        union all 
        select elems from to_upd
    ) j
)
update test_table set 
    data = jsonb_set(data,'{certified}',elems)
from final_array fa
where clusterid = 1;
1 rows affected

select * 
from test_table;


clusterid
owner
data


2
Bob
null

3
Wolf
null

1
Jacky
{"certified": [{"Type": "New Instance", "id_no": "f6b2197c4889", "Status": "Active", "created": "2020-02-03"}, {"Type": "New Instance", "id_no": "3bb515f781c6", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "26ae740e9b21", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "7e30a80211a2", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "216430036272", "Status": "Active", "created": "2020-02-04"}], "in_common": [{"name": "Kodali", "owner": "Jet"}, {"name": "Lakers", "owner": "Fantastic"}]}

clusterid	owner	data
1	Jacky	{"certified": [{"Type": "New Instance", "id_no": "f6b2197c4889", "Status": "Active", "created": "2020-02-03"}, {"Type": "New Instance", "id_no": "216430036272", "Status": "Failed", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "3bb515f781c6", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "26ae740e9b21", "Status": "Active", "created": "2020-02-04"}, {"Type": "New Instance", "id_no": "7e30a80211a2", "Status": "Active", "created": "2020-02-04"}], "in_common": [{"name": "Kodali", "owner": "Jet"}, {"name": "Lakers", "owner": "Fantastic"}]}
2	Bob	null
3	Wolf	null