Postgresql – Converting jsonb values to array

postgresql

I have a table with a column of type jsonb called attributes.

I have rows with values like –

{
    "id1": "something1",
    "id2": "something2",
    "id3": "something3",
}

and I want to convert them into –

{
    "id1": ["something1"],
    "id2": ["something2"],
    "id3": ["something3"],
}

How can I do that?

Best Answer

You would have to unroll it with jsonb_to_record or write a function in plv8,

SELECT jsonb_build_object(
  'id1', jsonb_build_array(id1),
  'id2', jsonb_build_array(id2),
  'id3', jsonb_build_array(id3)
) 
FROM (VALUES ($${
  "id1": "something1",
  "id2": "something2",
  "id3": "something3"
}$$::jsonb)) AS t(j)
CROSS JOIN jsonb_to_record(j)
  AS t2(id1 text, id2 text, id3 text);
Related Question