search
LoginSignup
30

More than 3 years have passed since last update.

posted at

updated at

Organization

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
30