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

• McNets: Have a look: stackoverflow.com/q/30707482/3270427
• Maxime: It's a JSON column. I already found this post, but didn't make it...
• Evan Carroll: That's not a json array, it's a json object.
• Jasen: 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.

Answer 1

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}

• Maxime: I finally took the time to implement your answer. And I would like to adapt it to do an upster, but I have an error message which is basicly (translated from French...) "Reference to the column "data" is ambiguous". Here is my query : INSERT INTO raw_data ("data") VALUES (%s) ON CONFLICT ("ID") DO UPDATE SET "data" = "data" || {'d':6}
• Evan Carroll: Data is non-reserved. Quoting it shouldn't make a difference. I would not quote data. And I would not quote ID. And I would make all of my columns lowercase (unquoted) @Maxime