ザッと俯瞰してみる。
成果物
情報源
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 |
---|---|---|
変換 | JSON→JSON。JSON要素を専用クエリで展開する | json_extract(json,path,...) |
変換 | JSON→表。JSONオブジェクト→表 | json_tree() |
変換 | JSON→表。JSON配列→表 | json_each() |
変換 | 表→JSON。行をJSON配列にする | json_group_array() |
変換 | 表→JSON。key とvalue に相当する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, 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()
のような関数がない。指定したキーをもつオブジェクトだけを取得したいときに難儀しそう。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習をはじめよう
- SQLite3学習 SQLiteについて
- SQLite3学習 SQLiteの適切な用途
- SQLite3学習 SQLiteの特徴
- SQLite3学習 SQLiteのクセ
- SQLite3学習 データ型とアフィニティ
- SQLite3学習 演算子の一覧
- SQLite3学習 よくある質問
- SQLite3学習 SQLiteダウンロード&コンパイル
- SQLite3学習 Tclで操作する
- SQLite3学習 ビルドオプション動作確認(SQLITE_ALLOW_URI_AUTHORITY)
- SQLite3学習 面白そうなコンパイルオプション
- SQLite3学習 SQLiteの拡張について