DuckDBを使ってJSONをSQLで処理

[DuckDB](https://github.com/duckdb/duckdb)（バージョンは 1.1.3）を使って JSON Lines ファイルを SQL で処理してみました。

DuckDB は組み込み型の分析用DBで、WebAssembly対応や高機能SQL[^1]などの気になる特徴がいくつかありますが、個人的にはデータ分析以外でも活用できそうな下記の点に注目しています。

* ローカルやリモートの CSV, JSON, Parquet ファイルや他のRDB（SQLite, PostgreSQL, MySQL）のデータをまとめて SQL で処理できる

[^1]: Python風のリスト内包表記が使える、コレクション関数（reduce, filterなど）が用意されている等

例えば、次のようにローカル SQLite ファイルのテーブルとリモートの JSON[^2]を join できたりします。

[^2]: JSON の products フィールドに配列として商品の内容が設定されている

```sql:sample.sql
ATTACH 'db/cart.db' AS cart (TYPE SQLITE);

SELECT
    cart_id, item_id, qty, p.title, p.price
FROM
    cart.cart_item AS c
    INNER JOIN (
        SELECT unnest(products) AS p FROM read_json('https://dummyjson.com/products')
    ) ON p.id = c.item_id
;
```
```sh:CLIによる実行結果
$ duckdb < sample.sql
┌─────────┬─────────┬───────┬───────────────────────────────┬────────┐
│ cart_id │ item_id │  qty  │             title             │ price  │
│  int64  │  int64  │ int64 │            varchar            │ double │
├─────────┼─────────┼───────┼───────────────────────────────┼────────┤
│       1 │       3 │     2 │ Powder Canister               │  14.99 │
│       1 │       6 │     1 │ Calvin Klein CK One           │  49.99 │
│       2 │       1 │     3 │ Essence Mascara Lash Princess │   9.99 │
└─────────┴─────────┴───────┴───────────────────────────────┴────────┘
```

# DuckDB 実行方法

インメモリで DuckDB を実行する場合、CLI ではこのように実行できます。

```sh:DuckDB CLI 実行例1
duckdb < <file>
```
```sh:DuckDB CLI 実行例2
duckdb -c <SQL>
```

Python や Node.js では次のようになります。
なお、Python では ```duckdb.sql(<SQL>).show()``` とすると整形した結果を出力できます。

```python:Python実行例
import duckdb

duckdb.sql(<SQL>)
```

```js:Node.js実行例（Neo API の場合）
import { DuckDBInstance } from '@duckdb/node-api'

const instance = await DuckDBInstance.create()
const con = await instance.connect()

const reader = await con.runAndReadAll(<SQL>)
```

# SQL処理

今回はこの JSON Lines ファイルを DuckDB で処理します。

```json:items.jsonl
{"id":1,"name":"item1","price":1100,"attrs":{"code":"0001","category":"A1"},"variants":[{"color":"white", "size":"S"},{"color":"black","size":"F"}]}
{"id":2,"name":"item2","price":2300,"attrs":{"code":"0002"},"variants":[{"color":"red"},{"color":"blue"},{"color":"white"}]}
{"id":3,"name":"item3","price":360,"attrs":{"code":"0003"}}
{"id":4,"name":"item4","price":4700,"attrs":{"code":"0004","category":"A1"},"variants":[{"color":"green", "size":"S"}]}
{"id":5,"name":"item5","price":590,"attrs":{"code":"0005","category":"B2"},"variants":[{"color":"red"},{"color":"white","size":"F"},{"color":"white","size":"L"}]}
{"id":6,"name":"item6","price":6200,"attrs":{"code":"0006","category":"C3"},"variants":[{"color":"blue","size":"S"},{"color":"green","size":"L"}]}
```

## (a) attrs, variants の出力

カレントディレクトリの items.jsonl から id、attrs の code、attrs、variants の値を抽出する SQL はこのようになります。

```sql
SELECT id, attrs.code, attrs, variants FROM items.jsonl WHERE id < 4
```

拡張子でファイルフォーマットを判定しているようなので、拡張子が ```.json``` や ```.jsonl``` となっていれば ```read_json``` 等を使わなくても FROM でファイル名を直接指定できます。

Python で show した結果はこのようになります。

```text:Python実行結果
┌───────┬─────────┬──────────────────────┬───────────────────────────────────────────────────────────┐
│  id   │  code   │        attrs         │                         variants                          │
│ int64 │ varchar │ struct(code varcha…  │           struct(color varchar, size varchar)[]           │
├───────┼─────────┼──────────────────────┼───────────────────────────────────────────────────────────┤
│     1 │ 0001    │ {'code': 0001, 'ca…  │ [{'color': white, 'size': S}, {'color': black, 'size': …  │
│     2 │ 0002    │ {'code': 0002, 'ca…  │ [{'color': red, 'size': NULL}, {'color': blue, 'size': …  │
│     3 │ 0003    │ {'code': 0003, 'ca…  │ NULL                                                      │
└───────┴─────────┴──────────────────────┴───────────────────────────────────────────────────────────┘
```

attrs が Struct 型、variants が Struct の List 型[^3]となっています。

Struct 内のカラムは　```attrs.code``` のように ```.<カラム>``` で指定できます。

[^3]: Array 型もあるが、DuckDB の Array は固定サイズとなっている

## (b) attrs.category の条件指定

attrs の category が 'A1' のものだけを抽出する SQL です。

```sql
SELECT id, name, attrs.category FROM items.jsonl WHERE attrs.category = 'A1'
```

実行結果はこのようになります。

```text:Python実行結果
┌───────┬─────────┬──────────┐
│  id   │  name   │ category │
│ int64 │ varchar │ varchar  │
├───────┼─────────┼──────────┤
│     1 │ item1   │ A1       │
│     4 │ item4   │ A1       │
└───────┴─────────┴──────────┘
```

## (c) variants の条件指定

variants 内に ```color='white'``` を含むものを抽出しようとしたところで工夫が必要になりました。

List 型では ```variants.color``` や ```variants[].color```、```variants[*].color``` のような指定はできないため、次のような方法で考えてみました。

* unnest関数の利用
* リスト内包表記やList用の関数利用

### unnest関数の利用

```unnest``` 関数を適用すると、List や Array の各要素を行へ展開できます。[^4]

[^4]: Struct を unnest すると Struct 内のカラム（フィールド）がカラムへ展開できます

```sh:unnest例
$ duckdb -c 'SELECT [1, 2, 3] as a, unnest([4, 5, 6]) as b' 
┌───────────┬───────┐
│     a     │   b   │
│  int32[]  │ int32 │
├───────────┼───────┤
│ [1, 2, 3] │     4 │
│ [1, 2, 3] │     5 │
│ [1, 2, 3] │     6 │
└───────────┴───────┘
```

これを利用すると、次のような方法で実現できました。

#### 例1

まずは ```EXISTS``` を使った方法です。

```sql
SELECT id, name FROM items.jsonl 
WHERE
    EXISTS (FROM (SELECT unnest(variants) AS v) WHERE v.color = 'white')
```

結果はこのようになります。

```text:Python実行結果
┌───────┬─────────┐
│  id   │  name   │
│ int64 │ varchar │
├───────┼─────────┤
│     5 │ item5   │
│     2 │ item2   │
│     1 │ item1   │
└───────┴─────────┘
```

何度実行しても同じ順序になります。

他のデータで試したところ元データの並びとは無関係な順序になったため、逆順になるわけでは無さそうです。

#### 例2

サブクエリを使って ```DISTINCT``` する方法です。

```sql
SELECT DISTINCT id, name
FROM
    (SELECT id, name, unnest(variants) AS v FROM items.jsonl)
WHERE
    v.color = 'white'
```

```WITH``` を使う事も可能です。

```sql
WITH x AS (SELECT id, name, unnest(variants) AS v FROM items.jsonl)
SELECT DISTINCT id, name FROM x WHERE v.color = 'white' 
```

DISTINCT すると、結果の順序が実行の度に変化します。

```text:Python実行結果
┌───────┬─────────┐
│  id   │  name   │
│ int64 │ varchar │
├───────┼─────────┤
│     2 │ item2   │
│     1 │ item1   │
│     5 │ item5   │
└───────┴─────────┘
```

ちなみに、DISTINCT しない場合の順序は毎回同じで、このような結果となります。

```text:Python実行結果（DISTINCTしない場合）
┌───────┬─────────┐
│  id   │  name   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ item1   │
│     2 │ item2   │
│     5 │ item5   │
│     5 │ item5   │
└───────┴─────────┘
```

### リスト内包表記やList用の関数利用

unnest を使わずにリスト内包表記や関数を利用する方法でいくつか考えてみました。

こちらの方法では結果の順序が実行の度に変化するような事はありませんでした。

#### 例1

リスト内包表記と ```list_contains``` 関数を組み合わせた方法です。

```sql
SELECT id, name FROM items.jsonl 
WHERE
    list_contains([x.color FOR x IN variants], 'white')
```
```text:Python実行結果
┌───────┬─────────┐
│  id   │  name   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ item1   │
│     2 │ item2   │
│     5 │ item5   │
└───────┴─────────┘
```

#### 例2

```list_filter``` 関数を使った方法です。

```sql
SELECT id, name FROM items.jsonl 
WHERE
    len(list_filter(variants, x -> x.color = 'white')) > 0
```
```txt:Python実行結果
┌───────┬─────────┐
│  id   │  name   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ item1   │
│     2 │ item2   │
│     5 │ item5   │
└───────┴─────────┘
```

#### 例3

リスト内包表記と ```ANY``` を組み合わせた方法です。

```sql
SELECT id, name FROM items.jsonl 
WHERE
    'white' = ANY ([x.color FOR x in variants])
```

こちらは毎回このような順序となりました。

```txt:Python実行結果
┌───────┬─────────┐
│  id   │  name   │
│ int64 │ varchar │
├───────┼─────────┤
│     1 │ item1   │
│     5 │ item5   │
│     2 │ item2   │
└───────┴─────────┘
```