Convert two columns to json

Question

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.

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.

SELECT 
('{' ||       -- 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

FROM (
-- 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.