MariaDB如何关联JSON数组元素字段并从另一表取值

示例表结构与测试数据 #

先创建并填充测试表，方便验证效果：

Table A #

CREATE TABLE TableA (
    id INT PRIMARY KEY,
    data JSON
);

INSERT INTO TableA VALUES
(1, '[{"b_id": 1}, {"b_id": 2}, {"b_id": 3}]'),
(2, '[{"b_id": 2}, {"b_id": 4}]');

Table B #

CREATE TABLE TableB (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO TableB VALUES
(1, 'Apple'),
(2, 'Banana'),
(3, 'Cherry'),
(4, 'Durian');

实现查询的SQL语句 #

利用MariaDB 11.2支持的JSON函数，先展开JSON数组，关联Table B获取名称，再重新聚合为数组：

SELECT 
    ta.id,
    JSON_ARRAYAGG(
        JSON_MERGE_PATCH(elem, JSON_OBJECT('name', tb.name))
    ) AS updated_data
FROM 
    TableA ta,
    JSON_TABLE(
        ta.data,
        '$[*]' COLUMNS (
            b_id INT PATH '$.b_id'
        )
    ) AS elem
LEFT JOIN TableB tb ON elem.b_id = tb.id
GROUP BY ta.id;

查询结果说明 #

执行后会得到每个TableA记录对应的更新后JSON数组，每个元素保留原b_id并添加对应TableB的name：

• id=1的updated_data：[{"b_id":1,"name":"Apple"},{"b_id":2,"name":"Banana"},{"b_id":3,"name":"Cherry"}]
• id=2的updated_data：[{"b_id":2,"name":"Banana"},{"b_id":4,"name":"Durian"}]

关键函数解释 #

• JSON_TABLE：将JSON数组拆分为行级数据，提取每个元素的b_id字段
• LEFT JOIN：关联TableB获取匹配的名称，若b_id无对应记录则name为NULL
• JSON_MERGE_PATCH：合并原JSON元素与新生成的{"name": ...}对象，保留原有字段的同时添加新字段
• JSON_ARRAYAGG：将关联后的行数据重新聚合为JSON数组

内容的提问来源于stack exchange，提问作者randomjohnny