Postgres 9.5

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

CREATE TABLE t (
    id int,
    mydata json
);

INSERT INTO t VALUES
(1, '[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]');

SELECT * FROM t


id
mydata


1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]



SELECT 1


SELECT
    *
FROM t,
    json_array_elements(t.mydata)


id
mydata
value


1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax": "yes", "tax_percent": 20, "used_when": "after" }

1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax": "no", "tax_percent": 20 }

1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax_percent": 20, "used_when": "before" }



SELECT 3


SELECT
    *
FROM t,
    json_array_elements(t.mydata) as elements,
    jsonb_set(elements::jsonb, '{tax}', 
        CASE
            WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb
            ELSE 'false'::jsonb
        END
    )
    


id
mydata
value
jsonb_set


1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax": "yes", "tax_percent": 20, "used_when": "after" }
{"tax": true, "used_when": "after", "tax_percent": 20}

1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax": "no", "tax_percent": 20 }
{"tax": false, "tax_percent": 20}

1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax_percent": 20, "used_when": "before" }
{"tax": false, "used_when": "before", "tax_percent": 20}



SELECT 3


SELECT
    *
FROM t,
    json_array_elements(t.mydata) as elements,
    jsonb_set(elements::jsonb, '{tax}', 
        CASE
            WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb
            ELSE 'false'::jsonb
        END
    ) as tax_updated,
    jsonb_set(tax_updated::jsonb, '{using}', 
        CASE
            WHEN tax_updated ->> 'used_when' = 'true' THEN 'true'::jsonb
            ELSE 'false'::jsonb
        END
    ) as using_updated


id
mydata
value
tax_updated
using_updated


1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax": "yes", "tax_percent": 20, "used_when": "after" }
{"tax": true, "used_when": "after", "tax_percent": 20}
{"tax": true, "using": false, "used_when": "after", "tax_percent": 20}

1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax": "no", "tax_percent": 20 }
{"tax": false, "tax_percent": 20}
{"tax": false, "using": false, "tax_percent": 20}

1
[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]
{ "tax_percent": 20, "used_when": "before" }
{"tax": false, "used_when": "before", "tax_percent": 20}
{"tax": false, "using": false, "used_when": "before", "tax_percent": 20}



SELECT 3


SELECT
    id,
    using_updated - 'used_when'
FROM t,
    json_array_elements(t.mydata) as elements,
    jsonb_set(elements::jsonb, '{tax}', 
        CASE
            WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb
            ELSE 'false'::jsonb
        END
    ) as tax_updated,
    jsonb_set(tax_updated::jsonb, '{using}', 
        CASE
            WHEN tax_updated ->> 'used_when' = 'true' THEN 'true'::jsonb
            ELSE 'false'::jsonb
        END
    ) as using_updated
 


id
?column?


1
{"tax": true, "using": false, "tax_percent": 20}

1
{"tax": false, "using": false, "tax_percent": 20}

1
{"tax": false, "using": false, "tax_percent": 20}



SELECT 3


SELECT
    id,
    json_agg((using_updated - 'used_when')::json) as new_data
FROM t,
    json_array_elements(t.mydata) as elements,
    jsonb_set(elements::jsonb, '{tax}', 
        CASE
            WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb
            ELSE 'false'::jsonb
        END
    ) as tax_updated,
    jsonb_set(tax_updated::jsonb, '{using}', 
        CASE
            WHEN tax_updated ->> 'used_when' = 'true' THEN 'true'::jsonb
            ELSE 'false'::jsonb
        END
    ) as using_updated
GROUP BY id


id
new_data


1
[{"tax": true, "using": false, "tax_percent": 20}, {"tax": false, "using": false, "tax_percent": 20}, {"tax": false, "using": false, "tax_percent": 20}]



SELECT 1


UPDATE t
SET mydata = s.new_data
FROM (
    SELECT
        id,
        json_agg((using_updated - 'used_when')::json) as new_data
    FROM t,
        json_array_elements(t.mydata) as elements,
        jsonb_set(elements::jsonb, '{tax}', 
            CASE
                WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb
                ELSE 'false'::jsonb
            END
        ) as tax_updated,
        jsonb_set(tax_updated::jsonb, '{using}', 
            CASE
                WHEN tax_updated ->> 'used_when' = 'true' THEN 'true'::jsonb
                ELSE 'false'::jsonb
            END
        ) as using_updated
    GROUP BY id
) s
WHERE s.id = t.id;

SELECT * FROM t


id
mydata


1
[{"tax": true, "using": false, "tax_percent": 20}, {"tax": false, "using": false, "tax_percent": 20}, {"tax": false, "using": false, "tax_percent": 20}]



SELECT 1

id	mydata	value
1	[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]	{ "tax": "yes", "tax_percent": 20, "used_when": "after" }
1	[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]	{ "tax": "no", "tax_percent": 20 }
1	[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]	{ "tax_percent": 20, "used_when": "before" }

id	mydata	value	jsonb_set
1	[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]	{ "tax": "yes", "tax_percent": 20, "used_when": "after" }	{"tax": true, "used_when": "after", "tax_percent": 20}
1	[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]	{ "tax": "no", "tax_percent": 20 }	{"tax": false, "tax_percent": 20}
1	[{ "tax": "yes", "tax_percent": 20, "used_when": "after" },{ "tax": "no", "tax_percent": 20 },{ "tax_percent": 20, "used_when": "before" }]	{ "tax_percent": 20, "used_when": "before" }	{"tax": false, "used_when": "before", "tax_percent": 20}

id	?column?
1	{"tax": true, "using": false, "tax_percent": 20}
1	{"tax": false, "using": false, "tax_percent": 20}
1	{"tax": false, "using": false, "tax_percent": 20}