AnalyticDB:JSON および JSONB データ型の操作

JSON および JSONB 共通の演算子

JSONB 専用の演算子

JSON 生成関数

JSON 処理関数

JSON データの格納およびクエリ実行

-- JSON および配列の列を含むテーブルを作成
CREATE TABLE tj (id serial, ary int[], obj json, num integer);

-- 行を挿入
INSERT INTO tj (ary, obj, num) VALUES ('{1,5}'::int[], '{"obj":1}', 5);
INSERT INTO tj (ary, obj, num) VALUES ('{2,5}'::int[], '{"obj":2}', 5);

-- 行を JSON オブジェクトに変換
SELECT row_to_json(q) FROM (SELECT id, ary, obj, num FROM tj) AS q;
--                  row_to_json
-- -------------------------------------------
--  {"f1":1,"f2":[1,5],"f3":{"obj":1},"f4":5}
--  {"f1":2,"f2":[2,5],"f3":{"obj":2},"f4":5}

JSON フィールドによるテーブル結合

結合条件で JSON フィールドを文字列として抽出するには、->> を使用します：

CREATE TABLE tj2 (id serial, ary int[], obj json, num integer);
INSERT INTO tj2 (ary, obj, num) VALUES ('{2,5}'::int[], '{"obj":2}', 5);

-- ->> 演算子を使用した結合
SELECT * FROM tj, tj2 WHERE tj.obj->>'obj' = tj2.obj->>'obj';

-- json_object_field_text() を使用した同等の結合
SELECT * FROM tj, tj2
  WHERE json_object_field_text(tj.obj, 'obj') = json_object_field_text(tj2.obj, 'obj');

JSON 列に対する関数インデックスの作成

関数インデックスを使用すると、JSON パス式の結果をインデックス化でき、ネストされたフィールドに対する効率的な等価検索が可能になります：

CREATE TEMP TABLE test_json (json_type text, obj json);
INSERT INTO test_json VALUES ('aa', '{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}');
INSERT INTO test_json VALUES ('cc', '{"f7":{"f3":1},"f8":{"f5":99,"f6":"foo"}}');

-- 抽出されたパス値に対するインデックスを作成
CREATE INDEX i ON test_json (json_extract_path_text(obj, '{f4}'));

-- 実行計画は等価クエリでこのインデックスを利用できます
SELECT * FROM test_json WHERE json_extract_path_text(obj, '{f4}') = '{"f5":99,"f6":"foo"}';
--  json_type |                 obj
-- -----------+-------------------------------------------
--  aa        | {"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}

JSONB 列における GIN インデックスのパフォーマンス比較

以下の例では、1,000 万行のデータを使用して、GIN インデックスが包含クエリに与える影響を示します。

-- テストテーブルを作成
CREATE TABLE jtest1 (id int, jdoc json);
CREATE TABLE jtest2 (id int, jdoc jsonb);
CREATE TABLE jtest3 (id int, jdoc jsonb);

-- ランダム文字列ヘルパー関数を作成
CREATE OR REPLACE FUNCTION random_string(INTEGER)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
    ARRAY (
        SELECT substring(
            '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
            FROM (ceil(random()*62))::int FOR 1
        )
        FROM generate_series(1, $1)
    ),
    ''
)
$BODY$
LANGUAGE sql VOLATILE;

-- jtest1 に 1,000 万行を挿入
INSERT INTO jtest1
SELECT t.seq,
       ('{"a":{"a1":"a1a1","a2":"a2a2"},"name":"' || random_string(10) || '","b":"bbbbb"}')::json
FROM generate_series(1, 10000000) AS t(seq);

-- JSONB テーブルにコピー
INSERT INTO jtest2 SELECT id, jdoc::jsonb FROM jtest1;
INSERT INTO jtest3 SELECT id, jdoc::jsonb FROM jtest1;

-- GIN インデックスを作成
CREATE INDEX idx_jtest2 ON jtest2 USING gin(jdoc);                        -- jsonb_ops
CREATE INDEX idx_jtest3 ON jtest3 USING gin(jdoc jsonb_path_ops);         -- jsonb_path_ops

クエリパフォーマンスの比較：

-- インデックスなしの JSON テーブルでの逐次スキャン：約 1,778 ms
EXPLAIN ANALYZE SELECT * FROM jtest1 WHERE jdoc @> '{"name":"N9WP5txmVu"}';

-- jsonb_ops を使用した GIN インデックス：約 1.3 ms
EXPLAIN ANALYZE SELECT * FROM jtest2 WHERE jdoc @> '{"name":"N9WP5txmVu"}';

-- jsonb_path_ops を使用した GIN インデックス：約 1.3 ms
EXPLAIN ANALYZE SELECT * FROM jtest3 WHERE jdoc @> '{"name":"N9WP5txmVu"}';

どちらの GIN インデックスタイプも、1,000 万行におけるクエリ時間を約 1,778 ms から約 1.3 ms まで短縮します。jsonb_path_ops は @> のみを使用するクエリに、jsonb_ops はキーの存在をチェックする演算子（?、?\|、?&）も併用するクエリにそれぞれ適しています。

Python からの JSONB データのクエリ実行

psycopg2 を使用して接続し、JSON クエリを実行します：

#!/usr/bin/env python
import json
import psycopg2

def run_query(sql):
    conn = psycopg2.connect("dbname=<your-database>")
    conn.autocommit = True
    cur = conn.cursor()
    cur.execute(sql)
    return cur.fetchall()

rows = run_query("SELECT obj FROM tj;")
for row in rows:
    print(json.loads(row[0]))

<your-database> をご利用のデータベース名に置き換えてください。