postgresql - How to convert JSON array field to a JSON object? - Stack Overflow

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

id	data
1	{"crud": "read", "description": "empdata", "info": {"empid": {"$in": [1, 2, 3]}}}

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

id	data
1	{"crud": "read", "description": "empdata", "info": {"empid": {"$in": [1, 2, 3]}}}

• json
• postgresql
• psql
• jsonb

Share Improve this question Follow edited Jan 29 at 11:09 Zegarek 27.4k55 gold badges2424 silver badges3030 bronze badges asked Jan 29 at 10:00 MadhuMadhu 4966 bronze badges 2

• 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

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

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;

id	jsonb_pretty
1	{     "crud": "read",     "info": {         "empid": {             "$in": [                 1,                 2,                 3             ]         }     },     "description": "empdata" }

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);

id	idx	e
1	1	{"crud": "read", "info": {"empid": {"$in": [1, 2, 3]}}, "description": "empdata"}
2	1	{"crud": "read1", "info": {"empid": {"$in": [4, 5, 6]}}, "description": "empdata1"}
2	2	{"crud": "read2", "info": {"empid": {"$in": [7, 8, 9]}}, "description": "empdata2"}

The answer is:

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