この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
PostgreSQLは9.2からJSONデータに対応しています。 スキーマを意識せずにデータを格納できるため、重宝する一方で、参照・更新時のアクセス方法が独特でとっつきにくいところもありました。
PostgreSQL 14から直感的なサブスクリプト(辞書/マップ)風のアクセスにも対応したため、この方法を紹介します。
ポイント
- PostgreSQL 14から連結配列風のサブスクリプトに対応。
- JSONB型のみ対応。JSON型は未対応
- 戻り値はJSONB型なので、適宜型をあわせる
やってみた
前提
PostgreSQL 14 を起動します。
test=> select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 14rc1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)
今回は AWS RDS PostgreSQL 14のパブリックプレビューを利用しました。
準備:検証データの投入
JSONデータを扱えるよう、JSONB型のカラムを含んだテーブルを作成します。
JSON型 は 14 からのサブスクリプト対応していないので、ご注意ください。
CREATE TABLE orders (
id serial,
info jsonb
);
INSERT INTO orders (info)
VALUES('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'),
('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');
SELECT * FROM orders;
id | info
----+-------------------------------------------------------------------------
1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(4 rows)
JSONデータを参照
JSON演算子方式
従来の JSON 演算子では、 ->
, ->>
などで属性にアクセスし、
->
の場合は、JSONB 型、 ->>
の場合は TEXT 型がかえってきました。
SELECT info -> 'items',
(info -> 'items' -> 'qty')::integer + 3
FROM orders
WHERE info ->> 'customer' = 'John Doe';
?column? | ?column?
-------------------------------+----------
{"qty": 6, "product": "Beer"} | 9
(1 row)
サブスクリプト方式
新しいサブスクリプトアクセスでは JSONB 型がかえってきます。 ::integer
や '"John Doe"'
で型をあわせています。
SELECT info['items'],
info['items']['qty']::integer + 3
FROM orders
WHERE info['customer'] = '"John Doe"';
-------------------------------+----------
{"qty": 6, "product": "Beer"} | 9
(1 row)
JSONデータを更新
サブスクリプトは更新にも使えます。
JSON演算子方式
JSON 演算子を使って属性を変更する場合、冗長・独特な構文を使う必要がありました。
UPDATE orders
SET info = jsonb_set(info::jsonb, '{items, product}', '"Wine"')
WHERE id = 1;
難易度の高さから、Stackoverflow でも、人気の質問の一つでした。
How do I modify fields inside the new PostgreSQL JSON datatype? - Stack Overflow
サブスクリプト方式
サブスクリプトを使うと、参照と同等の構文で更新できます。
UPDATE orders
SET info['items']['product'] = '"Wine"'
WHERE id = 1;
ソースコードリーディングの観点では、サブスクリプト構文のほうが、何をやっているのか理解しやすいのではないでしょうか?
JSON 型でサブスクリプト構文を利用するとエラーになる
JSON型のカラムではサブスクリプトを利用できないことを確認します。
test=> \d orders2
Table "public.orders2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('orders2_id_seq'::regclass)
info | json | | |
test=> select info['customer'] from orders2;
ERROR: cannot subscript type json because it does not support subscripting
LINE 1: select info['customer'] from orders2;
最後に
PostgreSQL 14からJSONデータをサブスクリプトでアクセスできるようになり、従来のJSON演算子やJSONPATHに比べ、参照・更新ともにより直感的な SQL 文をかけるようになりました。
サブスクリプトはJSONB型にのみ対応し、 JSON型には対応していません。
JSONB型はかんたんに GIN
インデックスを張れて、高速なアクセスが可能です。
PostgreSQLでJSONデータを扱う時に、JSON型よりもJSONB型を使う理由がまた一つ増えました。
それでは。