0

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

  • Have a look: stackoverflow.com/q/30707482/3270427 – McNets Dec 22 '18 at 21:17
  • It's a JSON column. I already found this post, but didn't make it... – Maxime Dec 22 '18 at 21:20
  • That's not a json array, it's a json object. – Evan Carroll Dec 22 '18 at 21:32
  • 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 Dec 22 '18 at 23:41
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}
  • 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} – Maxime Jan 14 '19 at 15:47
  • 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 – Evan Carroll Jan 14 '19 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.