LoginSignup
0
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?

@fits

DuckDBを使ってJSONをSQLで処理

Posted at

DuckDB（バージョンは 1.1.3）を使って JSON Lines ファイルを SQL で処理してみました。

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

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

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

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
;
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 ではこのように実行できます。

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

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

Python実行例
import duckdb

duckdb.sql(<SQL>)
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 で処理します。

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 はこのようになります。

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

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

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

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 のように .<カラム> で指定できます。

(b) attrs.category の条件指定

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

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

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

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

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 を使った方法です。

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

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

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

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

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

例2

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

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

WITH を使う事も可能です。

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

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

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

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

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

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

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

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

例1

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

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

例2

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

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

例3

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

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

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

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

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

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

  3. Array 型もあるが、DuckDB の Array は固定サイズとなっている ↩

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

0
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
0
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?