SQLite3のJSON機能でJSON演算子が使えるようになってMySQL, PostgreSQLと同じように便利になった

# はじめに
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レコードを挿入しておきます。 

```SQL:data.sql
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 コマンドでテーブルが作成されていることを確認しましょう。  

```SQL
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演算子がサポートされていないのでエラーとなります。

```SQL
sqlite> SELECT info->'$' FROM employees;
Error: in prepare, near ">": syntax error (1)
```

これまでバージョンでは、以下のSQL文のように、json_extract関数を使用して、jsonデータを取得していました。

```SQL
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 コマンドでテーブルが作成されていることを確認しましょう。  

```SQL
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データを取得することが可能です。

```SQL
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表現を返します。

```SQL
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です。

```SQL
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データ全体の取得、->演算子を使った子要素の取得、->>演算子を使った子要素の取得を実行しています。

```SQL
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データ全体の取得、->演算子を使った子要素の取得、->>演算子を使った子要素の取得を実行しています。

```SQL
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 への移行は注意が必要です。  

もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。

# 参照

以下の情報を参考にさせて頂きました。

- [SQLite 3.38 Improves JSON Queries, Error Messages, and More](https://www.infoq.com/news/2022/03/sqlite-3-38-json-queries/)
- [SQLite3 : JSON Functions And Operators](https://www.sqlite.org/json1.html)
- [PostgreSQL 13.1文書 : 9.16. JSON関数と演算子](https://www.postgresql.jp/document/13/html/functions-json.html)
- [MySQLでJSONを扱う](https://qiita.com/abcb2/items/8affae03caa3e94068b5)