Sqlite 3.9 の Json1を試した

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

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