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

4
  • Have a look: stackoverflow.com/q/30707482/3270427
    – McNets
    Commented Dec 22, 2018 at 21:17
  • It's a JSON column. I already found this post, but didn't make it...
    – Maxime
    Commented Dec 22, 2018 at 21:20
  • That's not a json array, it's a json object. Commented Dec 22, 2018 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
    Commented Dec 22, 2018 at 23:41

1 Answer 1

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}
2
  • 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
    Commented Jan 14, 2019 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 Commented Jan 14, 2019 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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