JSONデータを扱う

• MySQL

(8.0.31)
http://atudc.blogspot.com/2018/09/rdbmsoraclesql-servermysqljson.html

-- 1. テストテーブルとデータ作成

drop table tab1;

create table tab1 (
    col1 bigint,
    col2 json,
    constraint tab1pk primary key (col1)
);

insert into tab1 values (
1,
'{
    "col21" : 1,
    "col22" : "A",
    "col23" : [{"col231" : "123", "col232" : "XXX"},
                {"col231" : "456", "col232" : "YYY"}]
}');

select * from tab1;

-- 2. スカラー値取得

select t1.col2->'$.col21' col21,t1.col2->'$.col22' col22 from tab1 t1;

-- 3. オブジェクト取得

select t1.col2->'$.col23[0]' from tab1 t1;

select t1.col2->'$.col23[0].col231' from tab1 t1;

• Oracle

(19c)

http://atudc.blogspot.com/2018/09/rdbmsoraclesql-servermysqljson.html

https://qiita.com/kenwatan/items/1bb8e78cf311d9bbbd71
https://qiita.com/sugimount/items/064a1301b461df482f00
https://qiita.com/plusultra/items/377d810a6191ffe20045

-- 1. テストテーブルとデータ作成

drop table tab1 purge;

create table tab1 (
    col1   int,
    col2 varchar2(4000),
    constraint tab1pk primary key (col1),
    constraint tab1c01 check (col2 is json)
);

insert into tab1 values (
1,
'{
    "col21" : 1,
    "col22" : "A",
    "col23" : [{"col231" : "123", "col232" : "XXX"},
                {"col231" : "456", "col232" : "YYY"}]
}');

commit;

select * from tab1;

-- 2. スカラー値取得

select t1.col2.col21 col21, t1.col2.col22 col22 from tab1 t1;

select json_value(col2, '$.col21') col21, json_value(col2, '$.col22') col22 from tab1;

-- 3. オブジェクト取得

select  t1.col2.col23[0] from tab1 t1;
select json_query(col2, '$.col23[0]') from tab1;

select  t1.col2.col23[0].col231 from tab1 t1;
select json_query(col2, '$.col23[0].col231' with wrapper) from tab1;

• PostgreSQL

(15)

https://yu-report.com/entry/postgresqljson/

-- 1. テストテーブルとデータ作成

drop table tab1;

create table tab1 (
    col1 bigint,
    col2 jsonb,
    constraint tab1pk primary key (col1)
);

insert into tab1 values (
1,
'{
    "col21" : 1,
    "col22" : "A",
    "col23" : [{"col231" : "123", "col232" : "XXX"},
                {"col231" : "456", "col232" : "YYY"}]
}');

select * from tab1;

-- 2. スカラー値取得

select t1.col2 ->'col21' col21,t1.col2 ->'col22' col22 from tab1 t1;

-- 3. オブジェクト取得

select t1.col2->'col23'->0 from tab1 t1;

select t1.col2->'col23'->0->'col231' from tab1 t1;

• SQL Server

(2019)
http://atudc.blogspot.com/2018/09/rdbmsoraclesql-servermysqljson.html

-- 1. テストテーブルとデータ作成

drop table tab1;

create table tab1 (
    col1 bigint,
    col2 nvarchar(4000),
    constraint tab1pk primary key (col1),
    constraint tab1c01 check (isjson(col2) = 1)
);

insert into tab1 values (
1,
'{
    "col21" : 1,
    "col22" : "A",
    "col23" : [{"col231" : "123", "col232" : "XXX"},
                {"col231" : "456", "col232" : "YYY"}]
}');

select * from tab1;

-- 2. スカラー値取得

select json_value(col2, '$.col21') col21, json_value(col2, '$.col22') col22 from tab1;

-- 3. オブジェクト取得

select json_query(col2, '$.col23[0]') from tab1;

select json_value(json_query(col2, '$.col23[0]'), '$.col231') from tab1;