UI設計したり、ウェブシステムを開発したりします。 

SQL見るだけクエリ#061 SQLでJSONデータを扱う方法

記入日:2023-03-10 編集日:2023-03-10

SQL見るだけクエリ。SQLでJSONデータを扱う方法:SQLでJSON形式のデータを保存したり、取得したり、操作したりする方法です。

 

MySQL で JSON データを扱う:MySQL 5.7 以降では、JSON 型がサポートされており、JSON 形式のデータをテーブルに保存したり、 JSON 関数や演算子でデータの操作や抽出を行ったりすることができます。

 

MySQL で JSON データを扱う方法について、具体的なコードを交えて解説します。

MySQL で JSON 型を扱うには、型の指定に「JSON」と書くだけです。ただし、MySQL のバージョンは5.7以上である必要があります。 自分の MySQL のバージョンは下記コマンドで確かめられます。

```sql
SELECT VERSION();
```

次に、JSON データを保存するテーブルを作成します。例として、以下のようなテーブルを作成してみます。

```sql
CREATE TABLE json_contain (
    id INT PRIMARY KEY,
    json_data JSON
);
```

このテーブルでは、id という整数型の主キーと json_data という JSON 型の列があります。json_data には任意の JSON 形式のデータを保存することができます。

 

JSON データをテーブルに保存するには、INSERT 文を使って以下のように指定します。

```sql
INSERT INTO json_contain VALUES (1, '{"event" : "baseball", "tools" : "bat"}');
INSERT INTO json_contain VALUES (2, '{"event" : "tennis", "tools" : "racket"}');
```

JSON 形式 ({“key : value”, …})でデータを指定することで INSERT 処理が実行可能です。この場合、json_data 列には以下のようなデータが保存されます。

| id | json_data |
| -- | ----- |
| 1 | {"event": "baseball", "tools": "bat"} |
| 2 | {"event": "tennis", "tools": "racket"} |
 

JSON データから特定の値や要素を抽出するには、JSON 関数や演算子を使って以下のように指定します。

```sql
-- json_data 列から event の値だけ取り出す
SELECT id, json_data ->> '$.event' AS event FROM json_contain;

-- json_data 列から tools の値だけ取り出す
SELECT id, json_data ->> '$.tools' AS tools FROM json_contain;

-- json_data 列から event が 'baseball' の行だけ取り出す
SELECT * FROM json_contain WHERE json_data ->> '$.event' = 'baseball';
```
 

演算子は JSON 値から文字列値を抽出するために使われます。$.event や $.tools は JSON パス式と呼ばれるもので、 JSON ドキュメント内の特定の位置や要素を指定するために使われます。これらのクエリでは以下のような結果が得られます。

| id | event |
| -- | ---- |
| 1 | baseball |
| 2 | tennis |

| id | tools |
| -- | ---------- |
| 1 | bat |
| 2 | racket |

| id | json_data |
| -- | ----- |
| 1 | {"event": "baseball", "tools": "bat"} |
 

JSON データを更新するには、UPDATE 文や REPLACE 文などを使って以下のように指定します。

```sql
-- json_data 列から event を 'volleyball' に更新する
UPDATE json_contain SET json_data = JSON_REPLACE(json_data, '$.event', 'volleyball') WHERE id = 1;

-- json_data 列から tools を 'net' に置換する
REPLACE INTO json_contain VALUES (2, '{"event" : "tennis", "tools" : "net"}');
```

JSON_REPLACE 関数は JSON ドキュメント内の指定された位置や要素を別の値に置き換えるために使われます。 REPLACE 文は INSERT 文と同じように JSON 形式でデータを指定することで、既存の行を新しい行で置き換えることができます。これらのクエリでは以下のような結果が得られます。

| id | json_data |
| -- | ---- |
| 1 | {“event”: “volleyball”, “tools”: “bat”} |
| 2 | {“event”: “tennis”, “tools”: “net”} |

以上が、MySQL で JSON データを扱う方法の基本的な方法です。

SQLの使いどころ

サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。

SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。

また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。

KK

機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。