I want to know how to change a json array with an object in it into a jsonb object, in PostgreSQL.

For example: fiddle

create table test (
   id int generated by default as identity primary key
  ,data json);

INSERT INTO test (data)
VALUES ('[{ "crud": "read"
           ,"description": "empdata"
           ,"info": {
               "empid": {
                  "$in": [1, 2, 3]
               }
             }
           }
          ]');

I want the result to be shown as below

I want to know how to change a json array with an object in it into a jsonb object, in PostgreSQL.

For example: fiddle

create table test (
   id int generated by default as identity primary key
  ,data json);

INSERT INTO test (data)
VALUES ('[{ "crud": "read"
           ,"description": "empdata"
           ,"info": {
               "empid": {
                  "$in": [1, 2, 3]
               }
             }
           }
          ]');

I want the result to be shown as below

• json
• postgresql
• psql
• jsonb

• 1 what if the array is empty or contains more than one element? – mr mcwolf Commented Jan 29 at 10:25
• You mean there's always exactly one object in the array? – Bergi Commented Jan 29 at 17:36

3 Answers 3

You can use json_array_elements function like this:

SELECT j.*
FROM test t,
LATERAL json_array_elements(t.data) AS j;

If you want a id you can use this trick:

SELECT ROW_NUMBER() OVER (PARTITION BY t.id) AS id, j.* as value
FROM test t,
LATERAL json_array_elements(t.data) AS j;

Outputs

id  | value
1   | {"crud": "read", "description": "empdata", "info": {"empid": {"$in": [1, 2, 3]}}}
2   | {"second": "json"}

Full example

If you want to change the column data type as well as alter the structure of the JSON values it currently holds by unwrapping the single-element array layer, you can simply alter table with json array accessor ->0 and a ::jsonb cast:
_{demo at db<>fiddle}

alter table test alter column data type jsonb using((data->0)::jsonb);

This peels away the array wrapping as well as converts all json values you have in that table, to jsonb.

select id,jsonb_pretty(data::jsonb) from test;

All future entries will of course also be saved as jsonb.

If you want to keep the data type you have and only convert them on the fly in a select, for a single-element array, you can peel that layer with a ->0 just the same:

select id,(data->0)::jsonb
from test;

In cases when the array can have multiple elements, you can explode it with json_array_elements()::jsonb:

select id, json_array_elements(data)::jsonb
from test;

To also get the index of each element, move the set returning function down to the from list and add with ordinality clause:

select id, idx, e::jsonb
from test
cross join lateral json_array_elements(data)with ordinality as elems(e,idx);

The answer is:

SELECT 
    (data::jsonb -> 0) AS result 
FROM 
    test;