LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

コンピューターシステム株式会社Advent Calendar 2024

Day 3
@nimzo6689inコンピューターシステム株式会社

SQLite で利用できる JSON 関数の使い方総まとめ

Last updated at Posted at 2024-12-02

SQLite 3.47 (2024-10-21) 時点でサポートされている JSON 関数、全 30 個の使い方をまとめました。
公式ドキュメントを Qiita っぽく書いただけのような記事です。

セットアップ

この記事で書く SQL は、全て SQLite のインメモリで実行可能です。
サンプルコードと出力形式を合わせたい場合は、以下のドットコマンドを実行してください。

SQLite Shell
.mode table
.header on
.nullvalue (NULL)

この記事で解説する関数

お使いの SQLite で利用できる JSON 関数は以下の方法で確認できます。

SQLite Shell
WITH
  functions(name) AS (
    SELECT name
      FROM pragma_function_list  -- スカラー関数、集計関数
     UNION ALL
    SELECT name
      FROM pragma_module_list    -- Table-Valued 関数
     GROUP BY name
  ),
  json_functions(name) AS (
    SELECT name
      FROM functions
     WHERE name = 'json' OR name LIKE 'json$_%' ESCAPE '$'
  ),
  jsonb_functions(name) AS (
    SELECT name
      FROM functions
     WHERE name = 'jsonb' OR name LIKE 'jsonb$_%' ESCAPE '$'
  )
SELECT
       ROW_NUMBER() OVER (ORDER BY json.name) AS No,
       json.name AS json_functions,
       jsonb.name AS jsonb_functions
  FROM json_functions json
  LEFT JOIN jsonb_functions jsonb
    ON jsonb.name = (
      CASE
        WHEN instr(json.name, '_') > 0
        THEN substr(json.name, 1, instr(json.name, '_') - 1) || 'b' || substr(json.name, instr(json.name, '_'))
        ELSE json.name || 'b'
      END
    )
 GROUP BY json.name
 ORDER BY json.name
;

+----+---------------------+--------------------+
| No |   json_functions    |  jsonb_functions   |
+----+---------------------+--------------------+
| 1  | json                | jsonb              |
| 2  | json_array          | jsonb_array        |
| 3  | json_array_length   | (NULL)             |
| 4  | json_each           | (NULL)             |
| 5  | json_error_position | (NULL)             |
| 6  | json_extract        | jsonb_extract      |
| 7  | json_group_array    | jsonb_group_array  |
| 8  | json_group_object   | jsonb_group_object |
| 9  | json_insert         | jsonb_insert       |
| 10 | json_object         | jsonb_object       |
| 11 | json_patch          | jsonb_patch        |
| 12 | json_pretty         | (NULL)             |
| 13 | json_quote          | (NULL)             |
| 14 | json_remove         | jsonb_remove       |
| 15 | json_replace        | jsonb_replace      |
| 16 | json_set            | jsonb_set          |
| 17 | json_tree           | (NULL)             |
| 18 | json_type           | (NULL)             |
| 19 | json_valid          | (NULL)             |
+----+---------------------+--------------------+

JSON サポート状況

SQLite ではバージョン 3.38.0 (2022-02-22) 以降からデフォルトで JSON 関数が利用可能です。
それ以前（3.37.2 以降）は、コンパイル時に -DSQLITE_ENABLE_JSON1 オプションをつけることで利用できました。

また、バージョン 3.42.0 (2023-05-16) 以降からは JSON5 仕様にも対応しています。
そのため、以下のような JSON ファイルもパースできます。

json5-format.json
{
  // comments
  unquoted: 'and you can quote me on that',
  singleQuotes: 'I can use "double quotes" here',
  lineBreaks: "Look, Mom! \
No \\n's!",
  hexadecimal: 0xdecaf,
  leadingDecimalPoint: .8675309, andTrailing: 8675309.,
  positiveSign: +1,
  trailingComma: 'in objects', andIn: ['arrays',],
  "backwardsCompatible": "with JSON",
}

上の JSON5 は、SQLite で実行する際はシングルクォーテーションを '' に変換する必要があります。
例： SELECT json('{singleQuotes: ''I can use "double quotes" here"''}');

JSONB 関数について

3.45.0 (2024-01-15) から PostgreSQL を参考に SQLite でも JSONB が使えるようになりました。
JSONB はバイナリで保存されるのでパフォーマンスにおいてメリットがあります。

SQLite には普通の JSON 文字列を返す JSON 関数（json_*）と JSONB データを返す JSONB 関数（jsonb_*）があります。
基本的にどの関数も引数には JSON 文字列でも JSONB データでも対応していますが、
戻り値のデータ型として JSONB を返したい用途のある関数（例えば、json_array など）は、 jsonb_array のように対応する関数が用意されています。
逆に json_pretty のように JSONB データを返す用途がない関数の場合は、 JSONB 関数は用意されていません。

変換系の関数

json / jsonb

渡された JSON データを Minify された JSON 文字列に変換する。

項目 説明
引数１ json 文字列、または、JSONB バイナリデータ。
戻り値 MinifyされたJSON文字列。JSON5の場合は、JSON1の文字列に変換されて、Minifyされる。
使い方
SELECT json(
'{
  "this": "is",
  "a": ["test"]
}')
;

-- {"this":"is","a":["test"]}

json_pretty

渡された JSON 文字列を人間が読みやすいように整形する。
3.46.0 (2024-05-23) から利用可能です。

項目 説明
引数１ JSON 文字列、または、JSONB バイナリデータ。
引数２ （任意）インデントに使用する文字列。デフォルトはスペース４つ。
戻り値 整形された JSON 文字列。 JSON5 の場合は、 JSON1 の文字列に変換されて、整形される。
使い方
SELECT json_pretty('{"this":"is","a":["test"]}', ' ');

-- {
--  "this": "is",
--  "a": [
--   "test"
--  ]
-- }

json_object / jsonb_object

キーと値のペアから JSON オブジェクトを生成する。

項目 説明
引数１ キーと値のペアを交互に並べる。（可変長引数）
戻り値 JSON オブジェクト文字列。
使い方
VALUES
  (json_object('a',2,'c',4)),                 -- {"a":2,"c":4}
  (json_object('a',2,'c','{e:5}')),           -- {"a":2,"c":"{e:5}"}
  (json_object('a',2,'c',json_object('e',5))) -- {"a":2,"c":{"e":5}}
;

json_array / jsonb_array

渡された引数を JSON の配列形式に変換する。

項目 説明
引数１ 配列の１番目の要素に格納される。
引数Ｎ 配列のN番目の要素に格納される。
戻り値 配列形式の JSON が出力される。
使い方
VALUES 
  (json_array(1, 2, '3', 4)),                                     -- [1,2,"3",4]
  (json_array('[1,2]')),                                          -- ["[1,2]"]
  (json_array(json_array(1, 2))),                                 -- [[1,2]]
  (json_array(1, null, '3', '[4,5]', '{"six":7.7}')),             -- [1,null,"3","[4,5]","{\"six\":7.7}"]
  (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 文字列。
引数２ （任意）JSONPath。
戻り値 配列の個数。配列ではない値であれば、0 が返ります。
使い方
VALUES
  (json_array_length('[1,2,3,4]')),                -- 4
  (json_array_length('[1,2,3,4]', '$')),           -- 4
  (json_array_length('[1,2,3,4]', '$[2]')),        -- 0
  (json_array_length('{"one":[1,2,3]}')),          -- 0
  (json_array_length('{"one":[1,2,3]}', '$.one')), -- 3
  (json_array_length('{"one":[1,2,3]}', '$.two'))  -- (NULL)
;

json_quote

SQL 表現の値を JSON 表現の値に変換します。

項目 説明
引数１ SQL 表現の値。
戻り値 JSON 表現の値。
使い方
VALUES
  (json_quote(3.14159)),     -- 3.14159
  (json_quote('verdant')),   -- "verdant"
  (json_quote('[1]')),       -- "[1]"
  (json_quote(json('[1]'))), -- [1]
  (json_quote('[1,'))        -- "[1,"
;

検証系の関数

json_valid

文字列が有効な JSON かどうかを検証します。

項目 説明
引数１ 検証したい JSON 文字列。
引数２ オプションの検証モードを指定するビットマスク（省略時は1）。
戻り値 1: 妥当な JSON
0: 不正な JSON
NULL: 入力のいずれかが NULL の場合。

検証モードはこちらです。

ビットマスク 説明
1 RFC-8259 準拠の標準 JSON
2 JSON5 形式
4 JSONB の可能性がある
8 厳密な JSONB 形式
使い方
VALUES
  (json_valid('{"x":35}')), -- 1
  (json_valid('{x:35}')),   -- 0
  (json_valid('{x:35}',6)), -- 1
  (json_valid('{"x":35')),  -- 0
  (json_valid(NULL))        -- (NULL)
;

json_type

JSON 値の型を返す。

項目 説明
引数１ JSON 文字列
引数２ （任意）JSONPath。指定した場合、そのパスの値の型を返す。
戻り値 object, array, string, integer, real, true, false, null のいずれか。無効なJSON の場合は NULL 。
使い方
VALUES
  (json_type('{"a":[2,3.5,true,false,null,"x"]}')),          -- object
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$')),      -- object
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a')),    -- array
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]')), -- integer
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]')), -- real
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]')), -- true
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]')), -- false
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]')), -- null
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]')), -- text
  (json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]'))  -- (NULL)
;

json_error_position

JSON 文字列に構文エラーがあれば、何文字目にエラーがあるかを返します。
複数構文エラーがある場合は、最初のエラーに該当する場所が返ります。
構文エラーがなければ 0 が返ります。

バージョン 3.42.0 (2023-05-16) より追加されました。

項目 説明
引数１ JSON 文字列
戻り値 構文エラーがある場所。
使い方
-- 9文字目に構文エラー（:）がある JSON を渡す。
SELECT json_error_position('{"this"::"is","a":["test"]}');

-- 9

値取得系の関数

json_extract / jsonb_extract

JSON 文字列から指定したパスの値を抽出する。

項目 説明
引数１ JSON文字列
引数２以降 JSONPath（複数指定可）
戻り値 指定したパスの値。パスが存在しない場合は NULL 。複数パスを指定した場合は配列。
使い方
VALUES
  (json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$')),          -- {"a":2,"c":[4,5,{"f":7}]}
  (json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c')),        -- [4,5,{"f":7}]
  (json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]')),     -- {"f":7}
  (json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f')),   -- 7
  (json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a')),     -- [[4,5],2]
  (json_extract('{"a":2,"c":[4,5],"f":7}','$.c[#-1]')),      -- 5
  (json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x')),        -- (NULL)
  (json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a')), -- [null,2]
  (json_extract('{"a":"xyz"}', '$.a')),                      -- xyz
  (json_extract('{"a":null}', '$.a'))                        -- (NULL)
;

->> 演算子と-> 演算子

SQLite バージョン3.38.0（2022-02-22）以降にて、 json_extract() をより便利に書けるようにするため ->> 演算子と -> 演算子が追加されました。

json_extract() は、JSON 内の値が JSON の配列やオブジェクトの場合のみ JSON 表現を返し、JSON の値が JSON の null 、文字列、数値の場合は SQL 表現を返す一方で、
->> 演算子は常に SQL 表現、 -> 演算子は常に JSON 表現で返します。

使い方
VALUES
  ('{"a":2,"c":[4,5,{"f":7}]}' -> '$'),              -- '{"a":2,"c":[4,5,{"f":7}]}'
  ('{"a":2,"c":[4,5,{"f":7}]}' -> '$.c'),            -- '[4,5,{"f":7}]'
  ('{"a":2,"c":[4,5,{"f":7}]}' -> 'c'),              -- '[4,5,{"f":7}]'
  ('{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2]'),         -- '{"f":7}'
  ('{"a":2,"c":[4,5,{"f":7}]}' -> '$.c[2].f'),       -- '7'
  ('{"a":2,"c":[4,5,{"f":7}]}' ->> '$.c[2].f'),      -- 7
  ('{"a":2,"c":[4,5,{"f":7}]}' -> 'c' -> 2 ->> 'f'), -- 7
  ('{"a":2,"c":[4,5],"f":7}' -> '$.c[#-1]'),         -- '5'
  ('{"a":2,"c":[4,5,{"f":7}]}' -> '$.x'),            -- (NULL)
  ('[11,22,33,44]' -> 3),                            -- '44'
  ('[11,22,33,44]' ->> 3),                           -- 44
  ('{"a":"xyz"}' -> '$.a'),                          -- '"xyz"'
  ('{"a":"xyz"}' ->> '$.a'),                         -- 'xyz'
  ('{"a":null}' -> '$.a'),                           -- 'null'
  ('{"a":null}' ->> '$.a')                           -- (NULL)
;

更新系の関数

json_insert / jsonb_insert

JSON 文字列の指定したパスに値を挿入する。パスが既に存在する場合は何もしない。

項目 説明
引数１ 元となる JSON 文字列
引数２以降 JSONPath と値のペアを交互に指定（可変長引数）
戻り値 更新された JSON 文字列。無効な JSON またはパスの場合は NULL 。
使い方
VALUES
  (json_insert('{"a":2,"c":4}', '$.a', 99)), -- {"a":2,"c":4}
  (json_insert('{"a":2,"c":4}', '$.e', 99))  -- {"a":2,"c":4,"e":99}
;

json_replace / jsonb_replace

JSON 文字列の指定したパスの値を置換する。パスが存在しない場合は何もしない。

項目 説明
引数１ 元となる JSON 文字列
引数２以降 JSONPath と値のペアを交互に指定（可変長引数）
戻り値 更新された JSON 文字列。無効な JSON またはパスの場合は NULL 。
使い方
VALUES
  (json_replace('{"a":2,"c":4}', '$.a', 99)), -- {"a":99,"c":4}
  (json_replace('{"a":2,"c":4}', '$.e', 99))  -- {"a":2,"c":4}
;

json_set / jsonb_set

JSON 文字列の指定したパスに値を設定する。パスが存在しない場合は新規作成する。

項目 説明
引数１ 元となる JSON 文字列
引数２以降 JSONPath と値のペアを交互に指定（可変長引数）
戻り値 更新された JSON 文字列
使い方
VALUES
  (json_set('{"a":2,"c":4}', '$.a', 99)),                -- {"a":99,"c":4}
  (json_set('{"a":2,"c":4}', '$.e', 99)),                -- {"a":2,"c":4,"e":99}
  (json_set('{"a":2,"c":4}', '$.c', '[97,96]')),         -- {"a":2,"c":"[97,96]"}
  (json_set('{"a":2,"c":4}', '$.c', json('[97,96]'))),   -- {"a":2,"c":[97,96]}
  (json_set('{"a":2,"c":4}', '$.c', json_array(97, 96))) -- {"a":2,"c":[97,96]}
;

json_remove / jsonb_remove

JSON文字列から指定したパスの値を削除する。

項目 説明
引数１ 元となる JSON 文字列。
引数２以降 削除する JSONPath （可変長引数）。
戻り値 更新された JSON 文字列。無効なパスを指定した場合は NULL を返す。
使い方
VALUES
  (json_remove('[0,1,2,3,4]', '$[2]')),           -- [0,1,3,4] 
  (json_remove('[0,1,2,3,4]', '$[2]', '$[0]')),   -- [1,3,4]
  (json_remove('[0,1,2,3,4]', '$[0]', '$[2]')),   -- [1,2,4]
  (json_remove('[0,1,2,3,4]', '$[#-1]', '$[0]')), -- [1,2,3]
  (json_remove('{"x":25,"y":42}')),               -- {"x":25,"y":42}
  (json_remove('{"x":25,"y":42}', '$.z')),        -- {"x":25,"y":42}
  (json_remove('{"x":25,"y":42}', '$.y')),        -- {"x":25}
  (json_remove('{"x":25,"y":42}', '$'))           -- (NULL)
;

json_patch / jsonb_patch

RFC 7396 に準拠した JSON Merge Patch を適用する。

項目 説明
引数１ 元となる JSON 文字列（対象）
引数２ パッチとして適用する JSON 文字列（変更内容）
戻り値 パッチが適用された JSON 文字列。無効な JSON の場合は NULL 。
使い方
VALUES
  (json_patch('{"a":1,"b":2}','{"c":3,"d":4}')),                  -- {"a":1,"b":2,"c":3,"d":4}
  (json_patch('{"a":[1,2],"b":2}','{"a":9}')),                    -- {"a":9,"b":2}
  (json_patch('{"a":[1,2],"b":2}','{"a":null}')),                 -- {"b":2}
  (json_patch('{"a":1,"b":2}','{"a":9,"b":null,"c":8}')),         -- {"a":9,"c":8}
  (json_patch('{"a":{"x":1,"y":2},"b":3}','{"a":{"y":9},"c":8}')) -- {"a":{"x":1,"y":9},"b":3,"c":8}
;

集計関数

これまでスカラー値を扱う関数について見てきましたが、 JSON に特化した集計関数も利用可能です。
具体的には、json_group_array と json_group_object があります。

json_group_array / jsonb_group_array

行のグループから JSON 配列を生成する集約関数。

項目 説明
引数１ 配列の要素として使用する値（任意の型）
戻り値 JSON配列文字列。NULLの要素は含まれる。
使い方
WITH
  micans(name, age) AS (
    VALUES
      ('みきゃん', 13),
      ('ダークみきゃん', null)
  )
SELECT
  json_pretty(
    json_group_array(
      json_object('name', name, 'age', age)
    )
  )
  FROM micans
;
-- [
--     {
--         "name": "みきゃん",
--         "age": 13
--     },
--     {
--         "name": "ダークみきゃん
--         "age": null
--     }
-- ]

json_group_object / jsonb_group_object

行のグループからキーと値のペアを使用して JSON オブジェクトを生成する集約関数。

項目 説明
引数１ オブジェクトのキーとして使用する値（文字列型）
引数２ オブジェクトの値として使用する値（任意の型）
戻り値 JSONオブジェクト文字列。NULLの値は含まれる。
重複するキーは最後の値が使用される。
使い方
WITH
  settings(name, age) AS (
    VALUES
      ('name', 'my-package'),
      ('description', 'My fisrt package ever.'),
      ('private', true)
  )
SELECT
  json_pretty(
    json_group_object(name, age)
  )
  FROM settings
;
-- {
--     "name": "my-package",
--     "description": "My fisrt package ever
--     "private": 1
-- }

Table-Valued 関数

Table-Valued Function sは、関数から返されるデータをテーブルのように扱う便利な機能です。
例えばサブクエリの一部や JOIN 句で使用できます。

JSON 関連では json_each と json_tree が該当します。
ちなみに JSON 以外ですと、generate_series(start, stop, step) などがあります。

json_each

JSONオブジェクトの各要素を行として展開するテーブル値関数。

項目 説明
引数１ JSON 文字列
戻り値 各行に以下のカラムを持つテーブル

結果のカラム:

カラム名 説明
key キー名（文字列）
value 値（JSON 表現の文字列）
type 値の型（object, array, string, integer, real, true, false, null のいずれか）
atom 値が単一値（アトム）の場合その値、それ以外は NULL
id 配列インデックスまたは 0（オブジェクトの場合）
parent 入力 JSON への参照（内部使用）
fullkey JSONPath のフルパス（$.で始まる）
使い方
-- 基本的な使用例
SELECT * FROM json_each('{"name":"みきゃん","age":13}');
+------+---------+---------+----------+----+--------+---------+------+
| key  | value   |  type   | atom     | id | parent | fullkey | path |
+------+---------+---------+----------+----+--------+---------+------+
| name | みきゃん | text    | みきゃん | 2  |        | $.name  | $    |
| age  | 13      | integer | 13       | 21 |        | $.age   | $    |
+------+---------+---------+----------+----+--------+---------+------+

-- ネストしたオブジェクトの展開
SELECT key, value FROM json_each('{
   "name": "みきゃん",
   "address": {"prefecture":"愛媛県", "country":"日本"},
   "favorites": ["スポーツ", "旅行", "食べること"]
}');
-- +-----------+-----------------------------------------+
-- |    key    |                value                    |
-- +-----------+-----------------------------------------+
-- | name      | みきゃん                                 |
-- | address   | {"prefecture":"愛媛県","country":"日本"} |
-- | favorites | ["スポーツ","旅行","食べること"]          |
-- +-----------+-----------------------------------------+

json_tree

JSON オブジェクトまたは配列を再帰的に展開し、ネストした構造を木構造として返すテーブル値関数。

項目 説明
引数１ JSON 文字列
戻り値 各行に以下のカラムを持つテーブル

結果のカラム:

カラム名 説明
key キー名または配列インデックス（文字列）
value 値（JSON 表現の文字列）
type 値の型（object, array, string, integer, real, true, false, null のいずれか）
atom 値が単一値（アトム）の場合その値、それ以外は NULL
id 配列インデックスまたは 0（オブジェクトの場合）
parent 親要素へのパス
fullkey JSONPath のフルパス（$.で始まる）
使い方
-- 基本的な使用例（ネストしたオブジェクト）
SELECT fullkey, type, value FROM json_tree('{
   "name": "みきゃん",
   "address": {"prefecture":"愛媛県", "country":"日本"},
   "favorites": ["スポーツ", "旅行", "食べること"]
}')
 WHERE fullkey IS NOT '$'
;
-- +----------------------+--------+-------------------------------------+
-- |       fullkey        |  type  |                value                |
-- +----------------------+--------+-------------------------------------+
-- | $.name               | text   | みきゃん                                |
-- | $.address            | object | {"prefecture":"愛媛県","country":"日本"} |
-- | $.address.prefecture | text   | 愛媛県                                 |
-- | $.address.country    | text   | 日本                                  |
-- | $.favorites          | array  | ["スポーツ","旅行","食べること"]               |
-- | $.favorites[0]       | text   | スポーツ                                |
-- | $.favorites[1]       | text   | 旅行                                  |
-- | $.favorites[2]       | text   | 食べること                               |
-- +----------------------+--------+-------------------------------------+

参考

https://www.sqlite.org/json1.html

1
0
0

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
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?