How to convert element to array in jsonb [Postresql]-CodePudding

I need to convert some element in jsonb column to array

What I have:

{"a": {
          "b": "2022-11-03",
          "c": "321321",
          "d": "213321"
     }
}

What I need: `

{"a": [
        {
          "b": "2022-11-03",
          "c": "321321",
          "d": "213321"
         }
      ]
}

CodePudding user response：

you can use jsonb_set() for this:

SELECT jsonb_set(the_column, '{a}', jsonb_build_array(the_column -> 'a'))
FROM the_table

CodePudding user response：

Use the "json_agg" function.

select json_agg(DATA) from table_json

Example: db<>fiddle

CodePudding user response：

Using function from here JSON functions and the The SQL/JSON Path Language. Use path language to decompose the original object and then jsonb_build_object to rebuild it in new form.

SELECT
    jsonb_build_object(
        (jsonb_path_query_array('{"a": {"b": "2022-11-03","c": "321321","d": "213321"}}', '$.keyvalue()') -> 0 -> 'key') ->> 0, 
        (jsonb_path_query_array('{"a": {"b": "2022-11-03","c": "321321","d": "213321"}}', '$.keyvalue()') -> 0 -> 'value' 
         || '[]'::jsonb));

jsonb_build_object                     
------------------------------------------------------------
 {"a": [{"b": "2022-11-03", "c": "321321", "d": "213321"}]}