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

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

# セットアップ

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

```sql:SQLite Shell
.mode table
.header on
.nullvalue (NULL)
```

# この記事で解説する関数

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

```sql: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 ファイルもパースできます。

```javascript: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",
}
```

:::note warn
上の 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される。|

```sql:使い方
SELECT json(
'{
  "this": "is",
  "a": ["test"]
}')
;

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

## json_pretty

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

|項目|説明|
|---|---|
|引数１|JSON 文字列、または、JSONB バイナリデータ。|
|引数２|（任意）インデントに使用する文字列。デフォルトはスペース４つ。|
|戻り値|整形された JSON 文字列。 JSON5 の場合は、 JSON1 の文字列に変換されて、整形される。|

```sql:使い方
SELECT json_pretty('{"this":"is","a":["test"]}', ' ');

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

## json_object / jsonb_object

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

|項目|説明|
|---|---|
|引数１|キーと値のペアを交互に並べる。（可変長引数）|
|戻り値|JSON オブジェクト文字列。|

```sql:使い方
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 が出力される。|

```sql:使い方
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 が返ります。|

```sql:使い方
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 表現の値。|

```sql:使い方
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<br> 0: 不正な JSON<br> NULL: 入力のいずれかが NULL の場合。|

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

|ビットマスク|説明|
|---|---|
|1|RFC-8259 準拠の標準 JSON|
|2|JSON5 形式|
|4|JSONB の可能性がある|
|8|厳密な JSONB 形式|


```sql:使い方
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 。|

```sql:使い方
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 文字列|
|戻り値|構文エラーがある場所。|

```sql:使い方
-- 9文字目に構文エラー（:）がある JSON を渡す。
SELECT json_error_position('{"this"::"is","a":["test"]}');

-- 9
```

# 値取得系の関数

## json_extract / jsonb_extract

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

|項目|説明|
|---|---|
|引数１|JSON文字列|
|引数２以降|JSONPath（複数指定可）|
|戻り値|指定したパスの値。パスが存在しない場合は NULL 。複数パスを指定した場合は配列。|

```sql:使い方
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 表現で返します。

```sql:使い方
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 。|

```sql:使い方
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 。|

```sql:使い方
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 文字列|

```sql:使い方
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 を返す。|

```sql:使い方
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 。|

```sql:使い方
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の要素は含まれる。|

```sql:使い方
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の値は含まれる。<br> 重複するキーは最後の値が使用される。|

```sql:使い方
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 のフルパス（$.で始まる）|

```sql:使い方
-- 基本的な使用例
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 のフルパス（$.で始まる）|

```sql:使い方
-- 基本的な使用例（ネストしたオブジェクト）
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](https://www.sqlite.org/json1.html)