时间戳

首页 > 解决方案 > Mysql用JSON数组重写列

mysql - Mysql用JSON数组重写列

问题描述

我想编写一个 MySQL 查询，用另一个表中的数据替换 JSON 数组。

我有两张桌子，“预留”和“座位”。Reserved 包含一列“席位”，一个 JSON 数组，引用表“席位”的 ID。Table Seats 还包含一列“名称”。我现在想基本上将 Reserved Table 的 Seats 列的 JSON Data 中的 ID 替换为 Seats Table 中存储的相应 ID 的名称。

有没有办法在 Mysql 查询中做到这一点。我不知道如何以 JSON 格式打包查询结果并将其作为我已经尝试以某种方式使用 JSON_EXTRACT 的列返回：请参阅下面的测试。

SELECT * FROM `seats` WHERE ID = JSON_EXTRACT('["276", "277", "278"]','$.*')

基本上我想要一个这样的查询：

SELECT *, 
JSONCreate(SELECT name from `seats` WHERE seats.id IN JSON_EXTRACT(reserved.seats)) as name  
FROM `reserved` 
WHERE 1

标签： mysqljson

解决方案

您可以使用以下解决方案之一。

解决方案使用JSON_SEARCH和JSON_ARRAYAGG

SELECT r.seats, JSON_ARRAYAGG(s.name)
FROM reserved r JOIN seats s ON JSON_SEARCH(r.seats, 'one', CONVERT(s.id, CHAR(10))) IS NOT NULL
GROUP BY r.seats

解决方案使用... MEMBER OF ()和JSON_ARRAYAGG

SELECT r.seats, JSON_ARRAYAGG(s.name)
FROM reserved r INNER JOIN seats s ON CONVERT(s.id, CHAR) MEMBER OF(r.seats)
GROUP BY r.seats

解决方案使用JSON_CONTAINS和JSON_ARRAYAGG

SELECT r.seats, JSON_ARRAYAGG(s.name)
FROM reserved r INNER JOIN seats s ON JSON_CONTAINS(r.seats, JSON_QUOTE(CONVERT(s.id, CHAR))) = 1
GROUP BY r.seats

你也可以使用JSON_TABLE来解决这个问题：

SELECT JSON_ARRAYAGG(IFNULL(s.name, ''))
FROM reserved r, JSON_TABLE(
  r.seats,
  "$[*]" COLUMNS (
    id CHAR(50) PATH "$"
  )
) AS rr LEFT JOIN seats s ON rr.id = s.id
GROUP BY r.seats

注意：您可以使用INNER JOIN删除空值。而不是GROUP BY r.seats你应该使用一个id列。

dbfiddle.uk 上的演示

推荐阅读