SQLで複数のJSONを抽出し、並列に表示する方法

2年 ago
結衣, 春花
1 minute

SQLの複数のキーJSONを抽出するには、JSON_TABLE関数が使用できます。

JSONデータを変換するJSON_TABLE関数は以下のような構文です。

JSON_TABLE(json_column, '$[*]' COLUMNS (column1 data_type PATH '$.key1', column2 data_type PATH '$.key2', ...) ) AS table_alias

JSON_カラムは複数JSONが並列しているカラムのことで、 ‘$[*]’はJSONパス式で、全てのJSON要素を抽出することを表します。 COLUMNS句の後は各列の定義で、列名、データ型、JSONパスを指定します。

テーブル students に、info という名前の JSON 列があるとして、その列が JSON オブジェクトの配列を含むとします。各オブジェクトには name と age というプロパティがあるとします。

CREATE TABLE students (
  id INT PRIMARY KEY,
  info JSON
);

INSERT INTO students (id, info) VALUES (1, '[{"name": "Alice", "age": 20}, {"name": "Bob", "age": 21}, {"name": "Charlie", "age": 22}]');

SELECT s.id, jt.name, jt.age
FROM students s
CROSS JOIN JSON_TABLE(s.info, '$[*]' COLUMNS (
  name VARCHAR(50) PATH '$.name',
  age INT PATH '$.age'
)) AS jt;

クロス結合とJSON_TABLE関数を利用して、並列のJSONオブジェクトをリレーショナルテーブルに変換し、通常のSQLクエリステートメントを使用して操作できます。上記の例では、studentsテーブルから各生徒のID、名前、年齢のプロパティを抽出しました。

bannerAds