本文介绍了MySQL 加入 JSON 数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 mysql 表中有这些行
I have these rows in mysql table
id categoryNameSp categoryNameEn
1 Comida Food
2 Fruta Fruit
3 Fruta Seca Dried fruit
然后我在另一个表中有这一行
And then i have this row in another table
pid path
1 ["1", "2", "3"]
我想返回路径,但我想返回 categoryNameEn 而不是数字,因此返回将是:
I want to return the path but instead of numbers i want to return categoryNameEn so the return will be:
pid path
1 ["Food","Fruit", "Dried fruit"]
推荐答案
这需要使用 JSON_TABLE 功能以及 JSON_ARRAYAGG 聚合函数.
This would require to use the JSON_TABLE functionality as well as the JSON_ARRAYAGG aggregation function.
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"]');
然后我们可以做以下(花式)查询:
Then we can do the following (fancy) query:
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;
这个想法是根据 stuff
表中的 JSON 数据创建一个表.然后我们将它与类别表连接起来,并将结果聚合到一个 JSON 数组中.
The idea is to create a table from the JSON data found in the stuff
table. We then join it with the categories tables, and aggregate the result into a JSON array.
结果:
+------+----------------------------------+
| pid | JSON_ARRAYAGG(c.name) |
+------+----------------------------------+
| 1 | ["Food", "Fruit", "Dried Fruit"] |
+------+----------------------------------+
这篇关于MySQL 加入 JSON 数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!