Postgres 13

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?.

WITH mytable AS (
    SELECT '{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }'::json as mydata
)
SELECT * FROM mytable


mydata


{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }



SELECT 1


WITH mytable AS (
    SELECT '{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }'::json as mydata
)
SELECT 
    * 
FROM mytable,
    json_array_elements(mydata -> 'where')


mydata
value


{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }
{ "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }

{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }
{ "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] }



SELECT 2


WITH mytable AS (
    SELECT '{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }'::json as mydata
)
SELECT 
    * 
FROM mytable,
    json_array_elements(mydata -> 'where') array_elem
WHERE 
    array_elem ->> 'id' = 'Krishna'


mydata
value


{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }
{ "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }



SELECT 1


WITH mytable AS (
    SELECT '{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }'::json as mydata
)
SELECT 
    CASE WHEN array_elem ->> 'id' = 'Krishna' THEN
        jsonb_set(
             jsonb_set(array_elem.value::jsonb, '{function}', '"ADDITION"'::jsonb), 
             '{numValue}', 
             '["0","1"]'::jsonb
        )::json
    ELSE array_elem END
FROM mytable,
    json_array_elements(mydata -> 'where') array_elem
    


array_elem


{"id": "Krishna", "nick": "KK", "values": ["0"], "function": "ADDITION", "numValue": ["0", "1"]}

{ "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] }



SELECT 2


WITH mytable AS (
    SELECT '{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }'::json as mydata
)
SELECT 
    jsonb_agg(
        CASE WHEN array_elem ->> 'id' = 'Krishna' THEN
            jsonb_set(
                 jsonb_set(array_elem.value::jsonb, '{function}', '"ADDITION"'::jsonb), 
                 '{numValue}', 
                 '["0","1"]'::jsonb
            )
        ELSE array_elem::jsonb END
    )
FROM mytable,
    json_array_elements(mydata -> 'where') array_elem


jsonb_agg


[{"id": "Krishna", "nick": "KK", "values": ["0"], "function": "ADDITION", "numValue": ["0", "1"]}, {"id": "Krishna1", "nick": "KK1", "values": ["0"], "function": "SUB", "numValue": ["0"]}]



SELECT 1


WITH mytable AS (
    SELECT '{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }'::json as mydata
)
SELECT
    jsonb_set(
        (SELECT mydata::jsonb FROM mytable), 
        '{where}', 
        updated_array
    )::json
FROM (
    SELECT
        jsonb_agg(
            CASE WHEN array_elem ->> 'id' = 'Krishna' THEN
                jsonb_set(
                    jsonb_set(array_elem.value::jsonb, '{function}', '"ADDITION"'::jsonb), 
                    '{numValue}', 
                    '["0","1"]'::jsonb
                )
            ELSE array_elem::jsonb END
        ) as updated_array
    FROM mytable,
        json_array_elements(mydata -> 'where') array_elem
) s


jsonb_set


{"where": [{"id": "Krishna", "nick": "KK", "values": ["0"], "function": "ADDITION", "numValue": ["0", "1"]}, {"id": "Krishna1", "nick": "KK1", "values": ["0"], "function": "SUB", "numValue": ["0"]}], "TagTag": {"tt": "tttttt", "tt1": "tttttt"}, "sometag": {}, "anotherTag": []}



SELECT 1

mydata	value
{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }	{ "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }
{ "sometag": {}, "where": [ { "id": "Krishna", "nick": "KK", "values": [ "0" ], "function": "ADD", "numValue": [ "0" ] }, { "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] } ], "anotherTag": [], "TagTag": { "tt": "tttttt", "tt1": "tttttt" } }	{ "id": "Krishna1", "nick": "KK1", "values": [ "0" ], "function": "SUB", "numValue": [ "0" ] }