やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3学習 JSON拡張

 ザッと俯瞰してみる。

成果物

情報源

JSON拡張とは

SQL関数 概要 コード例 結果例
json(json) JSON文字列の縮小版を返す(余分な空白を削除) json('{ "A" : "a", "B": [ "B" ] } ') '{"this":"is","a":["test"]}'
json_array(value1,value2,...) JSON配列を返す json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')) '[1,null,"3",[4,5],{"six":7.7}]'
json_array_length(json)
json_array_length(json,path)
JSON配列の長さを返す json_array_length('[1,2,3,4]') 4
json_extract(json,path,...) 指定パスの値を取得する json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f')
json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a')
7
'[null,2]'
json_insert(json,path,value,...) データを新規に挿入する json_insert('{"a":2,"c":4}', '$.a', 99)
json_insert('{"a":2,"c":4}', '$.e', 99)
'{"a":2,"c":4}'
'{"a":2,"c":4,"e":99}'
json_object(label1,value1,...) オブジェクト形式にする json_object('a',2,'c',4) '{"a":2,"c":4}'
json_patch(json1,json2) マージする json_patch('{"a":[1,2],"b":2}','{"a":9}')
json_patch('{"a":[1,2],"b":2}','{"a":null}')
'{"a":9,"b":2}'
'{"b":2}'
json_remove(json,path,...) 削除する json_remove('[0,1,2,3,4]','$[2]')
json_remove('{"x":25,"y":42}','$.y')
'[0,1,3,4]'
'{"x":25}'
json_replace(json,path,value,...) 置換する json_replace('{"a":2,"c":4}', '$.a', 99)
json_replace('{"a":2,"c":4}', '$.e', 99)
'{"a":99,"c":4}'
'{"a":2,"c":4}'
json_set(json,path,value,...) 代入する json_set('{"a":2,"c":4}', '$.a', 99)
json_set('{"a":2,"c":4}', '$.e', 99)
json_set('{"a":2,"c":4}', '$.c', '[97,96]')
json_set('{"a":2,"c":4}', '$.c', json('[97,96]'))
json_set('{"a":2,"c":4}', '$.c', json_array(97,96))
'{"a":99,"c":4}'
'{"a":2,"c":4,"e":99}'
'{"a":2,"c":"[97,96]"}'
'{"a":2,"c":[97,96]}'
'{"a":2,"c":[97,96]}'
json_type(json)
json_type(json,path)
指定したデータのSQLiteにおける型を返す json_type('{"a":[2,3.5,true,false,null,"x"]}')
json_type('{"a":[2,3.5,true,false,null,"x"]}','$')
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a')
json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]')
json_type(...,'$.a[1]')
json_type(...,'$.a[2]')
json_type(...,'$.a[3]')
json_type(...,'$.a[4]')
json_type(...,'$.a[5]')
json_type(...,'$.a[6]')
'object'
'object'
'array'
'integer'
'real'
'true'
'false'
'null'
'text'
NULL
json_valid(json) 正しいJSON形式なら1、違えば0を返す json_valid('{"x":35}')
json_valid('{"x":35')
1
0
json_quote(value) 文字列型なら文字列としてダブルクォートする json_quote(3.14159)
json_quote('verdant')
3.14159
"verdant"

 編集用メソッドは以下の違いがある。

SQL関数 既存なら上書き? 無いなら作成?
json_insert(json,path,value,...) No Yes
json_replace(json,path,value,...) Yes No
json_set(json,path,value,...) Yes Yes

 修正箇所が複数あるならjson_patch(json1,json2)のほうが楽だろう。

集計

 集約内のすべての値から構成されるJSON配列やオブジェクトを返す集約SQL関数

table-valued関数

 上記関数が返すテーブルの構造は以下。json_each()したらjson_eachという名でjson_treeと同型のテーブルを返す。

CREATE TABLE json_tree(
    key ANY,             -- key for current element relative to its parent
    value ANY,           -- value for the current element
    type TEXT,           -- 'object','array','string','integer', etc.
    atom ANY,            -- value for primitive types, null for array & object
    id INTEGER,          -- integer ID for this element
    parent INTEGER,      -- integer ID for the parent of this element
    fullkey TEXT,        -- full path describing the current element
    path TEXT,           -- path to the container of the current row
    json JSON HIDDEN,    -- 1st input parameter: the raw JSON
    root TEXT HIDDEN     -- 2nd input parameter: the PATH at which to start
);
SELECT name FROM user WHERE phone LIKE '704-%'
UNION
SELECT user.name
  FROM user, json_each(user.phone)
 WHERE json_valid(user.phone)
   AND json_each.value LIKE '704-%';

変換

系統 概要 API
変換 JSON要素を専用クエリで展開する json_extract(json,path,...)
変換 JSONオブジェクト→表 json_tree()
変換 JSON配列→表 json_each()

カテゴリと優先度

系統 概要 API
変換 JSONJSONJSON要素を専用クエリで展開する json_extract(json,path,...)
変換 JSON→表。JSONオブジェクト→表 json_tree()
変換 JSON→表。JSON配列→表 json_each()
変換 表→JSON。行をJSON配列にする json_group_array()
変換 表→JSONkeyvalueに相当する2列の表からJSONオブジェクトに変換する json_group_object()
判定 JSON配列の要素数を返す json_array_length(json)
判定 JSON配列の型を返す json_type(json)
判定 正しいJSON形式なら1、違えば0を返す json_valid(json)
作成 JSON要素をマージしたテキストを返す json_patch(json1,json2)
作成 JSON要素を挿入したテキストを返す json_insert(json,path,value,...)
作成 JSON要素を削除したテキストを返す json_remove(json,path,...)
作成 JSON要素を置換したテキストを返す json_replace(json,path,value,...)
作成 JSON要素に代入したテキストを返す json_set(json,path,value,...)
作成 JSONオブジェクトの形式をしたテキストを返す json_object(label1,value1,...)
作成 JSON配列の形式をしたテキストを返す json_array(value1,value2,...)
作成 文字列型ならダブルクォートする json_quote(value)
作成 JSON整形(無駄スペース削除) json(json)
  1. 「変換」でJSON→表にしてselectする
  2. 「判定」で条件分けしてselectする
  3. 「作成」でJSON文字列を構築してupdateする

 1, 2でJSONから任意のデータを取得できる。これができれば大体OK。さらに3ができればJSONの追加・更新・削除もできる。

 つまり上記の順ほど学習の優先度が高い。

所感

優先

 JSONからデータを取得するには主に以下の関数を使うことになる。まずはこれらを使ってみよう。

関数 説明
json_extract() 指定した要素を取得する。ツリー階層., 配列インデックス[0]
json_each() 取得した行を配列用テーブルjson_eachへ変換する
json_tree() 取得した行を木用テーブルjson_treeへ変換する
json_group_array() 行を配列へ変換する
json_group_object() 行をオブジェクトへ変換する

不安

 副問合せ地獄になりそう……。

配列の要素を操作できない

 JSON拡張は配列の操作ができない。もし配列操作するならこちらのように面倒なことになる。追加も削除も更新もできないため、新たに配列を作り直して代入せねばならない。

has_key()がない

 has_key()のような関数がない。指定したキーをもつオブジェクトだけを取得したいときに難儀しそう。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで