SQLiteでJSONを扱う

SQLiteのドキュメントを久々に読み漁っていたら、いつの間にかJSONを扱える関数が生えていたので使ってみました。

テーブルのカラムにJSONオブジェクトそのものを格納して操作する方法は、ちょっと検索すればすぐに出てくるので、この記事では既存のRDBMSのデータ構造から入出力をJSONで扱う方法を中心にご紹介します。

テストデータ

CREATE TABLE IF NOT EXISTS "CAT" (
	"id"	INTEGER,
	"name"	TEXT,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "DETAIL" (
	"id"	INTEGER,
	"detail"	INTEGER,
	"field1"	TEXT,
	PRIMARY KEY("id","detail"),
	FOREIGN KEY("id") REFERENCES "TEST"("id")
);
CREATE TABLE IF NOT EXISTS "TEST" (
	"id"	INTEGER,
	"cat_id"	INTEGER,
	"description"	TEXT,
	PRIMARY KEY("id"),
	FOREIGN KEY("cat_id") REFERENCES "CAT"("id")
);
INSERT INTO "CAT" VALUES (1,'カテゴリ1');
INSERT INTO "CAT" VALUES (2,'カテゴリ2');
INSERT INTO "CAT" VALUES (3,'カテゴリ3');
INSERT INTO "DETAIL" VALUES (1,1,'テスト1-1');
INSERT INTO "DETAIL" VALUES (1,2,'テスト1-2');
INSERT INTO "DETAIL" VALUES (2,1,'テスト2-1');
INSERT INTO "DETAIL" VALUES (3,1,'テスト3-1');
INSERT INTO "DETAIL" VALUES (3,2,'テスト3-2');
INSERT INTO "DETAIL" VALUES (3,3,'テスト3-3');
INSERT INTO "TEST" VALUES (1,1,'データ1');
INSERT INTO "TEST" VALUES (2,2,'データ2');
INSERT INTO "TEST" VALUES (3,3,'データ3');

特に注意書きがない限り、結果セットは1行がSELECT結果の1行とします。

JSONデータ生成系

json_array()

結果をJSONの配列として返す関数です。引数は配列要素で、任意の数を指定できます。

SELECT json_array(id, description)
FROM TEST

[1,"データ1"]
[2,"データ2"]
[3,"データ3"]

json_object()

結果をJSONのオブジェクトとして返す関数です。引数は必ずKey-Valueのセットで指定し、奇数番目の引数がキー、偶数番目の引数が値となります。もちろんjson_array()同様、任意の数を指定できます。

SELECT json_object('id', id, 'description', description)
FROM TEST

{"id":1,"description":"データ1"}
{"id":2,"description":"データ2"}
{"id":3,"description":"データ3"}

キー名にもカラムを指定することができますが、指定できない型の場合は何も出力されません。下の例はキー名にid(INTEGER型)カラムを指定した例となります。

SELECT json_object(id, description)
FROM TEST

(結果セットなし)

文字列型にキャストすることで出力できるようになります。

SELECT json_object(cast(id AS TEXT), description)
FROM TEST

{"1":"データ1"}
{"2":"データ2"}
{"3":"データ3"}

json_group_array()

結果セットを配列としてグルーピングする関数です。SQLiteでJSONを取得する際の、最も肝となる関数かもしれません。

SELECT json_group_array(id)
FROM TEST

[1,2,3]

単カラム指定ではあまり恩恵がないかもしれませんが、ここまでの関数と組み合わせることで本領を発揮(?)します。

SELECT json_group_array(json_object('id', id, 'description', description))
FROM TEST

[{"id":1,"description":"データ1"},{"id":2,"description":"データ2"},{"id":3,"description":"データ3"}]

サブクエリでネストさせることもできます。

SELECT json_group_array(json_object(
  'id', id,
  'description', description,
  'detail', (
    SELECT json_group_array(field1)
	FROM DETAIL
	WHERE TEST.id = DETAIL.id
  ),
  'category', (
    SELECT json_object('category_id', CAT.id, 'name', name)
    FROM CAT
    WHERE TEST.cat_id = CAT.id
  )
))
FROM TEST

[
	{
		"id":1,
		"description":"データ1",
		"detail":["テスト1-1","テスト1-2"],
		"category":{"category_id":1,"name":"カテゴリ1"}
	},
	{
		"id":2,
		"description":"データ2",
		"detail":["テスト2-1"],
		"category":{"category_id":2,"name":"カテゴリ2"}
	},
	{
		"id":3,
		"description":"データ3",
		"detail":["テスト3-1","テスト3-2","テスト3-3"],
		"category":{"category_id":3,"name":"カテゴリ3"}
	}
]

※見やすいように出力に改行を入れています。実際の出力に改行は無く、結果セット1行としての出力です

json_group_object()

結果セットをオブジェクトとしてグルーピングする関数です。いわゆる辞書型のような結果を出力できます。

SELECT json_group_object(id, description)
FROM TEST

{"1":"データ1","2":"データ2","3":"データ3"}

JSONデータのパース系

json()

引数の文字がJSONであるかを確認し、正規化します。

SELECT JSON('a')

(結果セットなし)

SELECT JSON(json_str)
FROM (SELECT '{ "a" : "1" , "b" : "2" }' AS json_str)

{"a":"1","b":"2"}

-> ->>

JSONの文字列から指定の値を取り出す構文です。左辺にJSON文字列、右辺にパスを指定します。大なり1個の場合は、文字列型の場合の二重引用符がついたまま、大なり2個の場合は先頭と末尾の二重引用符が除去されます。パスの$はルートを表します。

SELECT json_str -> '$'
FROM (SELECT '{"a":"1"}' AS json_str)

{"a":"1"}

SELECT json_str -> '$.a'
FROM (SELECT '{"a":"1"}' AS json_str)

"1"

SELECT json_str ->> '$.a'
FROM (SELECT '{"a":"1"}' AS json_str)

1

SELECT json_str -> '$.b'
FROM (SELECT '{"b":[1,2,3]}' AS json_str)

[1,2,3]

入れ子になった要素も取得できます、

SELECT json_str -> '$.b[0]'
FROM (SELECT '"b":[1,2,3]}' AS json_str)

1

SELECT json_str -> '$.c.c1'
FROM (SELECT '{"c":{"c1":"test","c2":0}}' AS json_str)

"test"

json_each()

オブジェクトや配列をパースして仮想的な表に展開します。ちょうどjson_group_array()の逆のことをします。

SELECT *
FROM json_each('{"a":"1","b":[1,2,3],"c":{"c1":"test","c2":0}}')

INSERT文のSELECTステートメントに使ってみると下のようになります。

INSERT INTO TEST (id, description)
SELECT value ->> 'id' AS id, value ->> 'description' AS description
FROM json_each('[{"id":11,"description":"DESC1"},{"id":12,"description":"DESC2"}]')

もちろんUPSERTもできます。

INSERT INTO TEST (id, description)
SELECT value ->> 'id' AS id, value ->> 'description' AS description
FROM json_each('[{"id":3,"description":"DESC1"},{"id":4,"description":"DESC2"}]')
WHERE true
ON CONFLICT(id) DO UPDATE SET description = excluded.description

json_tree()

json_each()を再帰的に展開したものです。

SELECT *
FROM json_tree('{"a":"1","b":[1,2,3],"c":{"c1":"test","c2":0}}')