Postgres 11

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 mytable (
    myjson jsonb
);

INSERT INTO mytable VALUES
('[{"ch":"1","id":"12","area":"0","level":"Superficial","width":"","length":"","othern":"5","percent":"100","location":" 2nd finger base"},{"ch":"1","id":"13","area":"0","level":"Skin","width":"","length":"","othern":"1","percent":"100","location":" Abdomen "}]');

SELECT 
    jsonb_pretty(myjson) 
FROM mytable

CREATE TABLE



INSERT 0 1




jsonb_pretty


[
    {
        "ch": "1",
        "id": "12",
        "area": "0",
        "level": "Superficial",
        "width": "",
        "length": "",
        "othern": "5",
        "percent": "100",
        "location": " 2nd finger base"
    },
    {
        "ch": "1",
        "id": "13",
        "area": "0",
        "level": "Skin",
        "width": "",
        "length": "",
        "othern": "1",
        "percent": "100",
        "location": " Abdomen "
    }
]



SELECT 1


SELECT
    *
FROM
    mytable,
    jsonb_array_elements(myjson) elems


myjson
value


[{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "5", "percent": "100", "location": " 2nd finger base"}, {"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}]
{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "5", "percent": "100", "location": " 2nd finger base"}

[{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "5", "percent": "100", "location": " 2nd finger base"}, {"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}]
{"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}



SELECT 2


SELECT
    CASE WHEN elems ->> 'othern' = '5' THEN
        jsonb_set(elems, '{othern}', '"7"')
    ELSE elems END
FROM
    mytable,
    jsonb_array_elements(myjson) elems


elems


{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "7", "percent": "100", "location": " 2nd finger base"}

{"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}



SELECT 2


SELECT
    jsonb_agg(
        CASE WHEN elems ->> 'othern' = '5' THEN
            jsonb_set(elems, '{othern}', '"7"')
        ELSE elems END
    ) as json_array
FROM
    mytable,
    jsonb_array_elements(myjson) elems


json_array


[{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "7", "percent": "100", "location": " 2nd finger base"}, {"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}]



SELECT 1


UPDATE mytable
SET myjson = s.json_array
FROM (
    SELECT 
        jsonb_agg(
             CASE WHEN elems ->> 'othern' = '5' THEN
                 jsonb_set(elems, '{othern}', '"7"')
             ELSE elems  END
        ) as json_array
    FROM
        mytable,
        jsonb_array_elements(myjson) elems
) s;

SELECT 
    jsonb_pretty(myjson) 
FROM mytable

UPDATE 1




jsonb_pretty


[
    {
        "ch": "1",
        "id": "12",
        "area": "0",
        "level": "Superficial",
        "width": "",
        "length": "",
        "othern": "7",
        "percent": "100",
        "location": " 2nd finger base"
    },
    {
        "ch": "1",
        "id": "13",
        "area": "0",
        "level": "Skin",
        "width": "",
        "length": "",
        "othern": "1",
        "percent": "100",
        "location": " Abdomen "
    }
]



SELECT 1

myjson	value
[{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "5", "percent": "100", "location": " 2nd finger base"}, {"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}]	{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "5", "percent": "100", "location": " 2nd finger base"}
[{"ch": "1", "id": "12", "area": "0", "level": "Superficial", "width": "", "length": "", "othern": "5", "percent": "100", "location": " 2nd finger base"}, {"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}]	{"ch": "1", "id": "13", "area": "0", "level": "Skin", "width": "", "length": "", "othern": "1", "percent": "100", "location": " Abdomen "}