Question 90809 in PostgreSQL Question posted in
The official documentation can be found here.

How to convert element to array in jsonb [Postresql] – Postgresql

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"
         }
      ]
}
Tags:

3

Answers


  1. 0

    Use the "json_agg" function.

    select json_agg(DATA) from table_json

    Example: db<>fiddle

    Login or Signup to reply.
  2. 0

    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"}]}
    Login or Signup to reply.
  3. 0

    you can use jsonb_set() for this:

    SELECT jsonb_set(the_column, '{a}', jsonb_build_array(the_column -> 'a'))
FROM the_table
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search