MariaDBでJSON_ARRAYAGG関数を使いこなす！COLUMN_JSONからJSON配列を生成

2024-04-02

MariaDBで複数のCOLUMN_JSON結果をJSON配列として返す方法

JSON_ARRAYAGG関数を使う

MariaDB 10.2.7以降では、JSON_ARRAYAGG関数を用いて、複数のJSON値をJSON配列として返すことができます。

SELECT JSON_ARRAYAGG(column_json) AS json_array
FROM table_name;

この例では、table_nameテーブルのcolumn_json列のすべての値をJSON配列として返します。

例:

CREATE TABLE my_table (
  id INT,
  data JSON
);

INSERT INTO my_table (id, data) VALUES
  (1, '{"name": "John Doe", "age": 30}'),
  (2, '{"name": "Jane Doe", "age": 25}');

SELECT JSON_ARRAYAGG(data) AS json_array
FROM my_table;

結果:

[{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 25}]

GROUP_CONCAT関数を使う

JSON_ARRAYAGG関数が利用できない場合は、GROUP_CONCAT関数を使ってJSON配列を生成することができます。

SELECT GROUP_CONCAT(column_json ORDER BY id) AS json_array
FROM table_name
GROUP BY id;

CREATE TABLE my_table (
  id INT,
  data JSON
);

INSERT INTO my_table (id, data) VALUES
  (1, '{"name": "John Doe", "age": 30}'),
  (1, '{"name": "Jane Doe", "age": 25}'),
  (2, '{"name": "Peter Smith", "age": 40}');

SELECT GROUP_CONCAT(data ORDER BY id) AS json_array
FROM my_table
GROUP BY id;

[{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 25}],
[{"name": "Peter Smith", "age": 40}]

FOR JSON PATHを使う

MariaDB 10.3.14以降では、FOR JSON PATH構文を使ってJSON配列を生成することができます。

SELECT JSON_ARRAY(
  FOR JSON PATH '$[*]'
  FROM table_name
);

この例では、table_nameテーブルのすべての行をJSONオブジェクトとして扱い、それらをJSON配列にまとめます。

CREATE TABLE my_table (
  id INT,
  data JSON
);

INSERT INTO my_table (id, data) VALUES
  (1, '{"name": "John Doe", "age": 30}'),
  (2, '{"name": "Jane Doe", "age": 25}');

SELECT JSON_ARRAY(
  FOR JSON PATH '$[*]'
  FROM my_table
);

[{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 25}]

MariaDBで複数のCOLUMN_JSON結果をJSON配列として返すには、いくつかの方法があります。それぞれの方法には長所と短所があるので、状況に合わせて最適な方法を選択する必要があります。

JSON_ARRAYAGG関数: 最もシンプルで効率的な方法です。ただし、MariaDB 10.2.7以降でのみ利用可能です。
GROUP_CONCAT関数: どのバージョンのMariaDBでも利用可能ですが、JSON_ARRAYAGG関数よりも処理速度が遅くなる可能性があります。
FOR JSON PATH: 柔軟性の高い方法です。ただし、他の方法よりも複雑な構文を使用する必要があります。

これらの方法を参考に、目的に合った方法でJSON配列を生成してください。

サンプルテーブル:

CREATE TABLE my_table (
  id INT,
  data JSON
);

INSERT INTO my_table (id, data) VALUES
  (1, '{"name": "John Doe", "age": 30}'),
  (2, '{"name": "Jane Doe", "age": 25}'),
  (3, '{"name": "Peter Smith", "age": 40}');

JSON_ARRAYAGG関数を使う

SELECT JSON_ARRAYAGG(data) AS json_array
FROM my_table;

[{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 25}, {"name": "Peter Smith", "age": 40}]

GROUP_CONCAT関数を使う

SELECT GROUP_CONCAT(data ORDER BY id) AS json_array
FROM my_table
GROUP BY id;

[{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 25}],
[{"name": "Peter Smith", "age": 40}]

FOR JSON PATHを使う

SELECT JSON_ARRAY(
  FOR JSON PATH '$[*]'
  FROM my_table
);

[{"name": "John Doe", "age": 30}, {"name": "Jane Doe", "age": 25}, {"name": "Peter Smith", "age": 40}]

他の方法

自作関数を使う

SQLの自作関数を使って、JSON配列を生成することができます。

DELIMITER //

CREATE FUNCTION my_json_array(
  data JSON
) RETURNS JSON
BEGIN
  DECLARE json_array JSON;

  SET json_array = JSON_ARRAY();

  FOR i IN 1..JSON_LENGTH(data) DO
    SET json_array = JSON_ARRAY_APPEND(json_array, JSON_EXTRACT(data, CONCAT('$', i)));
  END FOR;

  RETURN json_array;
END //

DELIMITER ;

SELECT my_json_array(data) AS json_array
FROM my_table;