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]}}} |
- 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
Reset to default 1You 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;