时间戳

首页 > 解决方案 > MySQL 加入 JSON 数组

mysql - MySQL 加入 JSON 数组

问题描述

我在 mysql 表中有这些行

id  categoryNameSp    categoryNameEn
1   Comida            Food
2   Fruta             Fruit
3   Fruta Seca        Dried fruit

然后我在另一个表中有这一行

pid  path
1    ["1", "2", "3"]

我想返回路径，但我想返回 categoryNameEn 而不是数字，所以返回将是：

  pid  path
  1    ["Food","Fruit", "Dried fruit"]

标签： mysqljson

解决方案

这需要使用JSON_TABLE功能以及JSON_ARRAYAGG聚合函数。

使用以下DML和DDL 重新创建您的情况：

CREATE TABLE categories (id INT, name VARCHAR(30));
INSERT INTO categories VALUES (1, 'Food'), (2, 'Fruit'), (3, 'Dried Fruit');

CREATE TABLE stuff (pid INT, path JSON);
INSERT INTO stuff VALUES (1, '["1", "2", "3"]');

然后我们可以做以下（花哨的）查询：

SELECT pid, JSON_ARRAYAGG(c.name) FROM stuff AS s
    JOIN JSON_TABLE(s.path, '$[*]' COLUMNS (category INT PATH '$')) AS cats
    JOIN categories AS c ON cats.category = c.id
    GROUP BY pid;

这个想法是从表中找到的 JSON 数据创建一个stuff表。然后我们将它与类别表连接起来，并将结果聚合到一个 JSON 数组中。

结果：

+------+----------------------------------+
| pid  | JSON_ARRAYAGG(c.name)            |
+------+----------------------------------+
|    1 | ["Food", "Fruit", "Dried Fruit"] |
+------+----------------------------------+

推荐阅读