SQLite3 JSONから要素を取得する

概要

SQLite3ではJSON Pathを使用してJSONから要素を取得することが出来る。
ただし、ver.3.40.1 の時点で一つのJSONから一つの要素しか取得出来ず、 フィルタ等を使って複数の要素を取得することなどは出来ない。

構文

JSONから要素を取得するには->もしくは->>と JSON Pathを使用する。

'JSON String'->'JSON Path'
'JSON String'->>'JSON Path'
                

実行例

環境

• Windows 10 64bit
• SQLite3 (3.40.1) Command-Line Shell

• SQLite3で使えるJSON Path

sqlite> select
   ...>     '{
   ...>         "a": [1,2,3] ,
   ...>         "b": {
   ...>             "c": [4,5,6] ,
   ...>             "d": [7,8,9]
   ...>         }
   ...>     }'
   ...>     -> '$.a'
   ...> ;
[1,2,3]
                

sqlite> select
   ...>     '{
   ...>         "a": [1,2,3] ,
   ...>         "b": {
   ...>             "c": [4,5,6] ,
   ...>             "d": [7,8,9]
   ...>         }
   ...>     }'
   ...>     -> '$.b.c[1]'
   ...> ;
5
                

sqlite> select
   ...>     '{
   ...>         "a": [1,2,3] ,
   ...>         "b": {
   ...>             "c": [4,5,6] ,
   ...>             "d": [7,8,9]
   ...>         }
   ...>     }'
   ...>     -> '$.b.c[#-1]'
   ...> ;
6
                

指定されたJSON Pathが存在しなかった場合はNULLになる

sqlite> .nullvalue [null]
sqlite> select
   ...>     '{
   ...>         "a": [1,2,3] ,
   ...>         "b": {
   ...>             "c": [4,5,6] ,
   ...>             "d": [7,8,9]
   ...>         }
   ...>     }'
   ...>     -> '$.x'
   ...> ;
[null]
                

• SQLite3 コマンドラインツールでnullの表示を見やすくする

->と->>の違い

->と->>の違いは以下の通り。

->	JSON形式で取得する
->>	SQL形式で取得する

コマンドラインツールだとわかりにくいが、表示モードをquote に変更するとわかりやすい。

• SQLite3 .mode コマンドの出力サンプル

sqlite> .nullvalue [null]
sqlite> .mode quote
sqlite> select '{"a":123}'->'$.a';
'123'
sqlite> select '{"a":123}'->>'$.a';
123
sqlite> select '{"a":"abcdefg"}'->'$.a';
'"abcdefg"'
sqlite> select '{"a":"abcdefg"}'->>'$.a';
'abcdefg'
sqlite> select '{"a":null}'->'$.a';
'null'
sqlite> select '{"a":null}'->>'$.a';
NULL
                

参考URL

• # JSONPath - XPath for JSON
  https://goessner.net/articles/JsonPath/
• the_and_operators
  https://www.sqlite.org/json1.html#the_and_operators