APIを作成する時は、アプリ側でJSONを組む事が多いですが、MySQLもPostgreSQLもJSOＮデータ型がある為、JSONデータを保存する場所としても便利です。データ保存時にはJSONデータのバリデーションや保存したJSONデータにインデックスを張ったり、制約を付ける事も可能です。
MySQLにおけるJSONデータ型と関数
MySQLは5.7からJSONデータをサポートして、MySQL8.0では更に機能追加や改善が施されています。
CREATE TABLE `T_JSON` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'PGの場合はSEQUENCE',
`document` JSON COMMENT 'JSONデータ',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into T_JSON(document) values('{"doc-id":1, "name": "ドキュメント番号1"}');- JSONデータはバイナリー型で、データを格納時にJSONのバリデーションが実施されます。
- Simple JSON Date Type in MySQL
mysql> CREATE TABLE trip (data JSON);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO trip VALUES ('{"id": 1, "name": "東京"}');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO trip VALUES ('{"id": 2, "name": "大阪"}');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO trip VALUES ('{"id": 3, "name": "福岡"}');
Query OK, 1 row affected (0.02 sec)
■ json_extract (省略形：column->path) による基本的な参照処理
mysql> select json_extract(data,'$.name') from trip;
+-----------------------------+
| json_extract(data,'$.name') |
+-----------------------------+
| "東京" |
| "大阪" |
| "福岡" |
+-----------------------------+
3 rows in set (0.00 sec)
mysql> select json_extract(data,'$.name')='東京' from trip;
+--------------------------------------+
| json_extract(data,'$.name')='東京' |
+--------------------------------------+
| 1 |
| 0 |
| 0 |
+--------------------------------------+
3 rows in set (0.00 sec)
mysql> select json_search(data,'one','東%') from trip;
+--------------------------------+
| json_search(data,'one','東%') |
+--------------------------------+
| "$.name" |
| NULL |
| NULL |
+--------------------------------+
3 rows in set (0.01 sec)
mysql> select json_extract(data,'$.name') from trip where json_extract(data,'$.name') = '東京';
+-----------------------------+
| json_extract(data,'$.name') |
+-----------------------------+
| "東京" |
+-----------------------------+
1 row in set (0.01 sec)
JSON Objectの参照と生成列
■ JSONデータと生成列の組み合わせ
mysql> CREATE TABLE trip_generated_column (
id int(11) NOT NULL AUTO_INCREMENT,
data JSON NOT NULL,
prefecture varchar(30) GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL,
PRIMARY KEY (id),
KEY idx_trip_prefecture (prefecture)
);
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> show create table trip_generated_column\G
*************************** 1. row ***************************
Table: trip_generated_column
Create Table: CREATE TABLE `trip_generated_column` (
`id` int NOT NULL AUTO_INCREMENT,
`data` json NOT NULL,
`prefecture` varchar(30) COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (json_extract(`data`,_utf8mb4'$.name')) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_trip_prefecture` (`prefecture`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.02 sec)
■ 上記テーブルへデータをINSERTして生成列を活用してSELECTしてみる。
mysql> INSERT INTO trip_generated_column(data) VALUES ('{"id": 1, "name": "東京"}');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO trip_generated_column(data) VALUES ('{"id": 2, "name": "大阪"}');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO trip_generated_column(data) VALUES ('{"id": 3, "name": "福岡"}');
Query OK, 1 row affected (0.01 sec)
mysql> select * from trip_generated_column;
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | "東京" |
| 2 | {"id": 2, "name": "大阪"} | "大阪" |
| 3 | {"id": 3, "name": "福岡"} | "福岡" |
+----+-----------------------------+------------+
3 rows in set (0.01 sec)
mysql> select * from trip_generated_column where prefecture = '"東京"';
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | "東京" |
+----+-----------------------------+------------+
1 row in set (0.01 sec)
mysql> select * from trip_generated_column where json_extract(data,'$.name') = '東京';
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | "東京" |
+----+-----------------------------+------------+
1 row in set (0.00 sec)
mysql> select * from trip_generated_column where cast(json_extract(data,'$.name') as char) = '"東京"';
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | "東京" |
+----+-----------------------------+------------+
1 row in set (0.00 sec)
■ JSON_EXTRACTと生成列からの参照を同じように比較するには？
ここでは、json_unquoteで生成列に格納されるデータからQuoteは外しています。(->>)
※ JSON処理とは関係ありませんが、データを実体化する為に生成列をSTOREDに変更しています。
mysql> CREATE TABLE trip_generated_column_unquote (
id int(11) NOT NULL AUTO_INCREMENT,
data JSON NOT NULL,
prefecture varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(data,'$.name'))) STORED,
PRIMARY KEY (id),
KEY idx_trip_prefecture (prefecture)
);
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> INSERT INTO trip_generated_column_unquote(data) VALUES ('{"id": 1, "name": "東京"}');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO trip_generated_column_unquote(data) VALUES ('{"id": 2, "name": "大阪"}');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO trip_generated_column_unquote(data) VALUES ('{"id": 3, "name": "福岡"}');
Query OK, 1 row affected (0.01 sec)
mysql> select * from trip_generated_column_unquote;
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | 東京 |
| 2 | {"id": 2, "name": "大阪"} | 大阪 |
| 3 | {"id": 3, "name": "福岡"} | 福岡 |
+----+-----------------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from trip_generated_column_unquote where json_extract(data,'$.name') = '東京';
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | 東京 |
+----+-----------------------------+------------+
1 row in set (0.00 sec)
mysql> select * from trip_generated_column_unquote where prefecture = '東京';
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | 東京 |
+----+-----------------------------+------------+
1 row in set (0.00 sec)備考：生成列をJSON_UNQUOTE + JSON_EXTRACTの省略形(->>)で作成しても同じです。
mysql> select * from trip_generated_column where data->>'$.name' = '東京';
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | "東京" |
+----+-----------------------------+------------+
1 row in set (0.00 sec)
■ 生成列と組み合わせる事でJSONデータの特定の識別子にインデックスを付ける事が出来ます。
※ JSONデータ＋生成列＋INDEXでJSONを高速に検索する事が可能です!!
mysql> show create table trip_generated_column_unquote\G
*************************** 1. row ***************************
Table: trip_generated_column_unquote
Create Table: CREATE TABLE `trip_generated_column_unquote` (
`id` int NOT NULL AUTO_INCREMENT,
`data` json NOT NULL,
`prefecture` varchar(30) COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (json_unquote(json_extract(`data`,_utf8mb4'$.name'))) STORED,
PRIMARY KEY (`id`),
KEY `idx_trip_prefecture` (`prefecture`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
mysql> explain select * from trip_generated_column_unquote where json_extract(data,'$.name') = '東京'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trip_generated_column_unquote
partitions: NULL
type: ref
possible_keys: idx_trip_prefecture
key: idx_trip_prefecture
key_len: 123
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from trip_generated_column_unquote where prefecture = '東京'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trip_generated_column_unquote
partitions: NULL
type: ref
possible_keys: idx_trip_prefecture
key: idx_trip_prefecture
key_len: 123
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
■ 複数値インデックスとJSONデータ
MySQL 8.0.17 では、InnoDB は複数値インデックスをサポートしています。 複数値インデックスは、値の配カラムを格納するカラムに定義されたセカンダリインデックスです。 「normal」 インデックスには、データレコードごとに 1 つのインデックスレコードがあります (1:1)。 複数値インデックスは、単一のデータレコードに対して複数のインデックスレコードを持つことができます (N:1)。 複数値インデックスは JSON 配列のインデックス付けを目的としています。
mysql> show create table trip_generated_column_unquote\G
*************************** 1. row ***************************
Table: trip_generated_column_unquote
Create Table: CREATE TABLE `trip_generated_column_unquote` (
`id` int NOT NULL AUTO_INCREMENT,
`data` json NOT NULL,
`prefecture` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,_utf8mb4'$.name'))) STORED,
PRIMARY KEY (`id`),
KEY `idx_trip_prefecture` (`prefecture`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> CREATE INDEX idx_name on trip_generated_column_unquote ((CAST(data->'$.name' AS char(20) ARRAY)));
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table trip_generated_column_unquote\G
*************************** 1. row ***************************
Table: trip_generated_column_unquote
Create Table: CREATE TABLE `trip_generated_column_unquote` (
`id` int NOT NULL AUTO_INCREMENT,
`data` json NOT NULL,
`prefecture` varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,_utf8mb4'$.name'))) STORED,
PRIMARY KEY (`id`),
KEY `idx_trip_prefecture` (`prefecture`),
KEY `idx_name` ((cast(json_extract(`data`,_utf8mb4'$.name') as char(20) array)))
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> select * from trip_generated_column_unquote;
+----+-----------------------------+------------+
| id | data | prefecture |
+----+-----------------------------+------------+
| 1 | {"id": 1, "name": "東京"} | 東京 |
| 2 | {"id": 2, "name": "大阪"} | 大阪 |
| 3 | {"id": 3, "name": "福岡"} | 福岡 |
| 4 | {"id": 4, "name": "Tokyo"} | Tokyo |
+----+-----------------------------+------------+
4 rows in set (0.00 sec)
mysql> select * from trip_generated_column_unquote where JSON_CONTAINS(data->'$.name','["Tokyo"]');
+----+----------------------------+------------+
| id | data | prefecture |
+----+----------------------------+------------+
| 4 | {"id": 4, "name": "Tokyo"} | Tokyo |
+----+----------------------------+------------+
1 row in set (0.00 sec)
mysql> explain select * from trip_generated_column_unquote where JSON_CONTAINS(data->'$.name','["Tokyo"]')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: trip_generated_column_unquote
partitions: NULL
type: range
possible_keys: idx_name
key: idx_name
key_len: 83
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql>
参照：複数値インデックス
■ リレーショナルテーブルからJSONデータ生成
mysql> select * from test;
+------+-------+
| id | note |
+------+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+------+-------+
3 rows in set (0.01 sec)
mysql> SELECT JSON_OBJECT('id',id,'note',note) from test;
+----------------------------------+
| JSON_OBJECT('id',id,'note',note) |
+----------------------------------+
| {"id": 1, "note": "test1"} |
| {"id": 2, "note": "test2"} |
| {"id": 3, "note": "test3"} |
+----------------------------------+
3 rows in set (0.00 sec)
mysql> SELECT JSON_ARRAY('id',id,'note',note) from test;
+---------------------------------+
| JSON_ARRAY('id',id,'note',note) |
+---------------------------------+
| ["id", 1, "note", "test1"] |
| ["id", 2, "note", "test2"] |
| ["id", 3, "note", "test3"] |
+---------------------------------+
3 rows in set (0.00 sec)
■ JSON関数の一例
JSON_ARRAYAGG(), JSON_OBJECTAGG(), JSON_PRETTY()
- WL#7987
JSON aggregation functions
https://dev.mysql.com/worklog/task/?id=7987
mysql> select body from T_JSON_DOC where id in (1,2);
+---------------------------------------------------------------------------------+
| body |
+---------------------------------------------------------------------------------+
| {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]} |
+---------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> select JSON_ARRAYAGG(body) from T_JSON_DOC where id in(1,2);
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_ARRAYAGG(body) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- JSON_OBJECTAGG
mysql> select id,body from T_JSON_DOC where id in(1,2);
+----+---------------------------------------------------------------------------------+
| id | body |
+----+---------------------------------------------------------------------------------+
| 1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| 2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]} |
+----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC where id in(1,2);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(id,body) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select id,body from T_JSON_DOC;
+----+---------------------------------------------------------------------------------+
| id | body |
+----+---------------------------------------------------------------------------------+
| 1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| 2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]} |
| 3 | {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]} |
| 4 | {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]} |
| 5 | {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]} |
+----+---------------------------------------------------------------------------------+
5 rows in set (0.01 sec)
mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[0]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
2 rows in set (0.01 sec)
mysql> mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[1]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}}
2 rows in set (0.00 sec)
mysql>- WL#9191
JSON_PRETTY function
https://dev.mysql.com/worklog/task/?id=9191
mysql> select body from T_JSON_DOC where id = 1\G
*************************** 1. row ***************************
body: {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}
1 row in set (0.00 sec)
mysql> select json_pretty(body) from T_JSON_DOC where id = 1\G
*************************** 1. row ***************************
json_pretty(body): {
"id": 1,
"name": "",
"price": 10000,
"Conditions": [
"NEW",
2015,
"Excellent"
]
}
1 row in set (0.00 sec)
mysql> - JSON_STORAGE_SIZE(json_val)
This function returns the number of bytes used to store the binary representation of a JSON document.
JSON_STORAGE_SIZE(): Return value (bytes)
JSON_STORAGE_FREE(): If no updates have yet been performed, this is 0, as expected.
mysql> SELECT
-> jcol,
-> JSON_STORAGE_SIZE(jcol) AS Size,
-> JSON_STORAGE_FREE(jcol) AS Free
-> FROM jtable;
SELECT
-> JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
-> JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
-> JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
-> JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
詳細はマニュアルを参照下さい。以下資料では、いくつかJSON関数のサンプルを紹介しています。
PostgreSQLにおけるJSONデータ型と関数
■ simple JSON Data Type in PostgreSQL
POC=# CREATE TABLE trip (data JSON);
CREATE TABLE
POC=# INSERT INTO trip VALUES ('{"id": 1, "name": "東京"}');
INSERT 0 1
POC=# INSERT INTO trip VALUES ('{"id": 2, "name": "大阪"}');
INSERT 0 1
POC=# INSERT INTO trip VALUES ('{"id": 3, "name": "福岡"}');
INSERT 0 1
■ JSON Extract (->)
POC=# select * from trip;
data
---------------------------
{"id": 1, "name": "東京"}
{"id": 2, "name": "大阪"}
{"id": 3, "name": "福岡"}
(3 rows)
POC=# select data::json->'id' as id from trip;
id
----
1
2
3
(3 rows)
POC=# select data::json->'name' as name from trip;
name
--------
"東京"
"大阪"
"福岡"
(3 rows)
POC=# select data::json->>'name' as name from trip;
name
------
東京
大阪
福岡
(3 rows)
POC=# select
data,
data::json->'id' as id,
data::json->'name' as name from trip;
data | id | name
---------------------------+----+--------
{"id": 1, "name": "東京"} | 1 | "東京"
{"id": 2, "name": "大阪"} | 2 | "大阪"
{"id": 3, "name": "福岡"} | 3 | "福岡"
(3 rows)
POC=# select
data,
data::json->'id' as id,
data::json->'name' as name from trip
where data::json->>'name' = '東京';
data | id | name
---------------------------+----+--------
{"id": 1, "name": "東京"} | 1 | "東京"
(1 row)
■ JSONデータと生成列の組み合わせでインデックスを利用する。
この例では、データ量が少ないのでインデックスを利用していませんが、PostgreSQLの生成列にもインデックスを付与する事は可能。
POC=# CREATE TABLE trip_generated_column (
POC(# data JSON,
POC(# name varchar(30) GENERATED ALWAYS AS (data::json->>'name') STORED
POC(# );
CREATE TABLE
POC=# INSERT INTO trip_generated_column(data) VALUES ('{"id": 1, "name": "東京"}');
INSERT 0 1
POC=# INSERT INTO trip_generated_column(data) VALUES ('{"id": 2, "name": "大阪"}');
INSERT 0 1
POC=# INSERT INTO trip_generated_column(data) VALUES ('{"id": 3, "name": "福岡"}');
INSERT 0 1
POC=# select * from trip_generated_column;
data | name
---------------------------+------
{"id": 1, "name": "東京"} | 東京
{"id": 2, "name": "大阪"} | 大阪
{"id": 3, "name": "福岡"} | 福岡
(3 rows)
POC=# select * from trip_generated_column where name = '東京';
data | name
---------------------------+------
{"id": 1, "name": "東京"} | 東京
(1 row)
POC=# explain analyze select * from trip_generated_column where name = '東京';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on trip_generated_column (cost=0.00..17.38 rows=3 width=110) (actual time=0.009..0.010 rows=1 loops=1)
Filter: ((name)::text = '東京'::text)
Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.130 ms
(5 rows)
POC=# CREATE INDEX idx_trip_generated_column_name ON trip_generated_column (name);
CREATE INDEX
POC=# explain analyze select * from trip_generated_column where name = '東京';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Seq Scan on trip_generated_column (cost=0.00..1.04 rows=1 width=110) (actual time=0.024..0.026 rows=1 loops=1)
Filter: ((name)::text = '東京'::text)
Rows Removed by Filter: 2
Planning Time: 1.629 ms
Execution Time: 0.065 ms
(5 rows)
POC=#
■ リレーショナルテーブルからJSONデータ生成
POC=# SELECT ROW('東京','大阪','福岡','名古屋','新青森','新函館北斗');
row
-------------------------------------------
(東京,大阪,福岡,名古屋,新青森,新函館北斗)
(1 row)
POC=# SELECT ROW_TO_JSON(ROW('東京','大阪','福岡','名古屋','新青森','新函館北斗'));
row_to_json
-------------------------------------------------------------------------------
{"f1":"東京","f2":"大阪","f3":"福岡","f4":"名古屋","f5":"新青森","f6":"新函館北斗"}
(1 row)
- データをJSONフォーマットで抽出
参照：