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. – Craig Ringer Aug 24 '15 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 '15 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. – Craig Ringer Aug 24 '15 at 13:21
up vote 0 down vote accepted

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.

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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