最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

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

programmeradmin0浏览0评论

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]}}}
Share Improve this question edited Jan 29 at 11:09 Zegarek 26.7k5 gold badges24 silver badges30 bronze badges asked Jan 29 at 10:00 MadhuMadhu 496 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

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;
发布评论

评论列表(0)

  1. 暂无评论