Edited at

Sqlite 3.9 の Json1を試した

More than 1 year has passed since last update.

Sqlite 3.9 から、カラム型としてJSONが追加されたので、 (※)JSON文字列をデータとして扱えるようになったので、基本的な書き方を試してみた。

https://www.sqlite.org/json1.html

※ 初稿の表現に誤りがありました。

 sqlite3におけるカラム型は、それ自体が値の性質を決定するものではありません。

 (参照:https://www.sqlite.org/datatype3.html の最初の方)


CREATE TABLE

CREATE TABLE json_test_table (

id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR UNIQUE,
json JSON -- NEW!
)


INSERT

json1 関数を利用したINSERT

INSERT INTO json_test_table (

name,
json
)
VALUES (
'test_name_1',
json(json_object( -- objectは{}で囲うオブジェクト型のこと
'key_for_int', 1,
'key_for_str', 'test_str',
'key_for_arr', json('[1, "a", 2, "b"]'), -- json()で囲わないと文字列として保存される
'key_for_obj', json('{"c":3, "d":4}'),
'attr_a', 'nice_attr'
))
);

json文字列でINSERT

INSERT INTO json_test_table (name, json)

VALUES (
'test_name_2',
json('{ -- json()は文字列も評価できる
"key_for_int":2,
"key_for_str":"test_str2",
"key_for_arr":[5, "e", 6, "f"],
"key_for_obj":{"g":7, "h":8},
"attr_b":"good_attr"
}'
)
);


SELECT

jsonの値を個別に取得

SELECT 

json_extract(json, '$.key_for_int') as json_int, -- ルートを$として、jsのアクセサの記法で値を取得する
json_extract(json, '$.key_for_str') as json_str,
json_extract(json, '$.key_for_arr[1]') as json_array_element_2,
json_extract(json, '$.key_for_obj.d') as json_array_object_d
FROM
json_test_table
WHERE
name = 'test_name_1'

jsonの値を条件に使用する

json_treeを利用した例。

値はjson_tree.valueで取得できる。

SELECT

tree.value as attr_b
FROM
json_test_table,
json_tree(json_test_table.json, '$.attr_b') as tree -- attr_bをjson_treeとして取得する
WHERE
tree.value IS NOT NULL

json_extractを利用した例。

SELECT

json_extract(json_test_table.json, '$.attr_b') as attr_b
FROM
json_test_table
WHERE
json_extract(json_test_table.json, '$.attr_b') IS NOT NULL

JSON型カラムのデータをjson文字列として取得する

json_treeにはjson文字列がjsonカラムに保存されている。

SELECT

tree.json
FROM
json_test_table,
json_tree(json_test_table.json) as tree
WHERE
name = 'test_name_1'

jsonの値でORDER BYする

SELECT

name
FROM
json_test_table
ORDER BY
json_extract(json, '$.key_for_str') DESC


所感

pros


  • json文字列が用意できれば保存するのは簡単

  • JSONでもソートや検索条件が指定できたり、他のデータと結合できるのは利点

cons


  • JSONデータの扱い方が簡便でない

  • アプリ向けにラッパーAPIを用意するのが大変そう

both


  • スキームを無視してキーが可変長なデータを扱える