How do I modify a JSON object?

Question

I have a table "raw_data" in which I have a field "data" containing a json array like:

{'a' : 1, 
'b' : 2,
'c' : 3}

I would like two different things:

Append a new item like {'d' : 4} at the end of the array, that would give me : {'a' : 1, 'b' : 2, 'c' : 3, 'd' : 4}
Update the key 'c' with the value 5 in this array, that would give me: {'a' : 1, 'b' : 2, 'c' : 5}
Upsert the already existing key 'd' with the value 6 in this array, that would give me: {'a' : 1, 'b' : 2, 'c' : 3, 'd' : 6}

I found this post, but couldn't manage to understand how it applies to my specific case

It's a JSON column. I already found this post, but didn't make it... — Maxime
– Maxime, Commented Dec 22, 2018 at 21:20
the short answer is you don't modification is only supported on jsonb, the long answer is, if you don't need the json features than jsonb does not have (eg: duplicate keys) convert to jsonb, modify, and convert back. — Jasen
– Jasen, Commented Dec 22, 2018 at 23:41

Evan Carroll · Accepted Answer · 2018-12-22 21:38:40Z

0

The post you linked to was mine. That was on an array, but the operator is the same for an object (||) concatentation.

Append a new item like {'d' : 4}

SELECT $${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}';

Update the key 'c' with the value 5

SELECT ($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || {"c":5}';

Upsert the already existing key 'd' with the value 6

SELECT (($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || '{"c":5}') || '{"d":6}';

End result,

{"a": 1, "b": 2, "c": 5, "d": 6}

answered Dec 22, 2018 at 21:38

Evan Carroll

65.8k50 gold badges263 silver badges520 bronze badges

1 Answer 1