I have a json column with a json array in the form of

[{key : "key", value : "value"}, {key : "key2", value : "value2"}] 

that I wish to convert to a new form

{"key" : "value", "key2" : "value2"}.

I can use the following query to get rows with key column and value column

SELECT json_array_elements(somejson)->'key', json_array_elements(somejson)->'value' FROM temp;

However I can't seem to come up with a way to convert that to a nice key-value JSON object. Since I'm on 9.3, I can't use json_object or json_build_object either.

  • 1
    Short answer: update, doing this with 9.3 will be painful at best. Aug 24, 2015 at 13:18
  • Thanks, that's what it seemed like, but I thought there might be some trick I hadn't considered, since I'm not primarily a DBA.
    – Kayaman
    Aug 24, 2015 at 13:20
  • 1
    You can probably do it with string concatenation, but ... ugh. I don' t recommend it. json support continues to mature, so you'll often find cases where "gee, I wish I could do X" is solved in a newer version. There's a backport of the 9.5 json enhancements to 9.4, but not 9.4 to 9.3. Aug 24, 2015 at 13:21

1 Answer 1


Well, it would have been a lot faster and easier with 9.4, but it's doable with 9.3 in simple situations.

('{' ||       -- Opening brace for the JSON object
string_agg(   -- Get all the key/value pairs together in the same string
concat_ws(':', '"' || a || '"', b || ''), ',') || '}')::json -- Concatenate values to "key" : "value" format

-- Extract the key and value from each array element
SELECT json_array_elements(myjson)->'key' a, 
json_array_elements(myjson)->'value' b FROM temp) AS FOO;

Not fun, but thankfully this is a one-time thing.

This can't be used to update a table, but you can create a temporary table and insert the values there first.

  • can you please share the 9.4 or say 9.6 version as well? Apr 26, 2018 at 11:58
  • There's probably a direct bytecast from 9.4 onwards, or at least a conversion function between them.
    – Kayaman
    May 1, 2018 at 17:26

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.