はじめに
SQLite3は、SQLite 3.9.0 からJSON機能が使用できたのですが、JSON演算子(-> , ->>)が使えませんでした。ついに SQLite 3.38.0 から MySQL や PostgreSQL と同じように JSON演算子が使えるようになりました。そこでサクっと使ってみました。
また、MySQL と PostgreSQL の JSON演算子 とも使用感を比較してみました。
確認で使用するデータのSQL
確認で使用するテーブルとレコードは以下のSQLを使用して作成します。エディタで、data.sqlを事前に作成しておきます。
具体的には整数型の id と JSON型 の info を持つ employees テーブルを作成します。サンプルデータとして、1レコードを挿入しておきます。
CREATE TABLE employees(id INTEGER PRIMARY KEY, info JSON);
INSERT INTO employees VALUES(1,
'{"lastName" : "Jiro",
"firstName" : "Kannai",
"address" : {"home" : {"city" : "Yokohama",
"zip" : "231-0033"},
"work" : {"street" : "12 Main",
"city" : "Yokohama",
"zip" : "231-0045"}},
"phone" : {"home" : "050-2020-1234",
"work" : "050-5050-5678"}}');
SQLite 3.38.0 より前のバージョンでのJSON演算子を使用
SQLite 3.38.0 より前のバージョンでのJSON演算子を使用してみます(SQLite 3.37.2)。
SQLite を起動して、事前に用意した data.sql を読み込んでテーブルとレコードを作成します。作成した後は念のために.tab コマンドでテーブルが作成されていることを確認しましょう。
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .read data.sql
sqlite> .tab
employees
次にJSON演算子を使用したSELECT文を実行します。JSON演算子がサポートされていないのでエラーとなります。
sqlite> SELECT info->'$' FROM employees;
Error: in prepare, near ">": syntax error (1)
これまでバージョンでは、以下のSQL文のように、json_extract関数を使用して、jsonデータを取得していました。
sqlite> SELECT json_extract(info,'$') FROM employees;
{"lastName":"Jiro","firstName":"Kannai","address":{"home":{"city":"Yokohama","zip":"231-0033"},"work":{"street":"12 Main","city":"Yokohama","zip":"231-0045"}},"phone":{"home":"050-2020-1234","work":"050-5050-5678"}}
SQLite 3.38.0 以降のバージョンでJSON演算子を使用
SQLite 3.38.0 以降のバージョンでのJSON演算子を使用してみます(SQLite 3.39.1)。
SQLite を起動して、事前に用意した data.sql を読み込んでテーブルとレコードを作成します。作成した後は念のために.tab コマンドでテーブルが作成されていることを確認しましょう。
SQLite version 3.39.1 2022-07-13 19:41:41
Enter ".help" for usage hints.
sqlite> .read data.sql
sqlite> .tab
employees
次にJSON演算子を使用したSELECT文を実行します。JSON演算子がサポートされているのでJSONデータを取得することが可能です。
sqlite> SELECT info->'$' FROM employees;
{"lastName":"Jiro","firstName":"Kannai","address":{"home":{"city":"Yokohama","zip":"231-0033"},"work":{"street":"12 Main","city":"Yokohama","zip":"231-0045"}},"phone":{"home":"050-2020-1234","work":"050-5050-5678"}}
次に ->演算子を使用して、要素 lastName の値を取得しましょう。いくつかの表現を使用することが可能です。->演算子は、選択した要素のJSON表現を返します。
sqlite> SELECT info->'$.lastName' FROM employees;
"Jiro"
sqlite> SELECT info->'$'->'lastName' FROM employees;
"Jiro"
sqlite> SELECT info->'lastName' FROM employees;
"Jiro"
次に ->>演算子を使用して、要素 lastName の値を取得しましょう。いくつかの表現を使用することが可能です。->>演算子は、選択した要素を表すSQL TEXT、INTEGER、REAL、またはNULL値を返します。今回はTEXTです。
sqlite> SELECT info->>'$'->>'lastName' FROM employees;
Jiro
sqlite> SELECT info->>'$.lastName' FROM employees;
Jiro
sqlite> SELECT info->>'lastName' FROM employees;
Jiro
MySQL と PostgreSQL で JSON演算子を使用
MySQL 8.0 と PostgreSQL14 でもJSON演算子を使用してみましょう。
SQLite3でも使用した data.sql を読み込んでテーブルとレコードを作成します。MySQL, PostgreSQL どちらもテーブル作成、レコード挿入が問題なくできました。
MySQL で JSON演算子を使用
まずは、MySQL 8.0 でのJSON演算子を使用してみます。以下のSQLでは、JSONデータ全体の取得、->演算子を使った子要素の取得、->>演算子を使った子要素の取得を実行しています。
mysql> SELECT info FROM employees;
info
{"phone": {"home": "050-2020-1234", "work": "050-5050-5678"}, "address": {"home": {"zip": "231-0033", "city": "Yokohama"}, "work": {"zip": "231-0045", "city": "Yokohama", "street": "12 Main"}}, "lastName": "Jiro", "firstName": "Kannai"}
mysql> SELECT info->'$.lastName' FROM employees;
info->'$.lastName'
"Jiro"
mysql> SELECT info->>'$.lastName' FROM employees;
info->>'$.lastName'
Jiro
MySQL では、->'$.子要素' と ->>'$.子要素' が使用できますが、->'子要素' と ->>'子要素' が使用できません。
PostgreSQL で JSON演算子を使用
まずは、PostgreSQL 14 でのJSON演算子を使用してみます。以下のSQLでは、JSONデータ全体の取得、->演算子を使った子要素の取得、->>演算子を使った子要素の取得を実行しています。
postgres=# SELECT info FROM employees;
info
-----------------------------------------------------
{"lastName" : "Jiro", +
"firstName" : "Kannai", +
"address" : {"home" : {"city" : "Yokohama", +
"zip" : "231-0033"}, +
"work" : {"street" : "12 Main",+
"city" : "Yokohama", +
"zip" : "231-0045"} +
}, +
"phone" : {"home" : "050-2020-1234", +
"work" : "050-5050-5678"} +
}
(1 row)
postgres=# SELECT info->'lastName' FROM employees;
?column?
----------
"Jiro"
(1 row)
postgres=# SELECT info->>'lastName' FROM employees;
?column?
----------
Jiro
(1 row)
PostgreSQL では、MySQL と逆で、->'$.子要素' と ->>'$.子要素' が使用できませんが、->'子要素' と ->>'子要素' が使用できます。
まとめ
SQLite 3.38 以降で、MySQL や PostgreSQL と同じように JSON演算子が使用できることを確認しました。JSON演算子と同じようなことが、json_extract関数でも実現可能なのですが、json_extract関数だとネストが深くなると、表現が長くなることと、ＪSON演算子は MySQL と PostgreSQL と互換性があること、表現が直観的ということもあり、JSON演算子が使用できることは個人的に大歓迎です。
また、MySQL と PostgreSQL の JSON演算子も確認してみました。JSON演算子を使用したSQLを MySQL, PostgreSQL から SQLite3 への移行は問題なさそうですが、SQLite3 から MySQL, PostgreSQL への移行は注意が必要です。
もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。
参照
以下の情報を参考にさせて頂きました。