如何动态地为 JSON_OBJECT() 提供键和值？

Question

我正在搜索"FOR JSON路径"；(SQL Server(在MySQL中的等效版本，并遇到以下链接-stackoverflow问题

评级最高的解决方案运行良好，但是否可以动态提供列和值？我有超过20列，手动添加每个键值对是繁忙的&不易维护。

SQL Server查询-

SELECT u.user_id, u.first_name,(
SELECT f.*
FROM files f
WHERE f.is_active = 1 AND f.user_id = u.user_id
FOR JSON PATH) as files
FROM users u
WHERE u.user_id = 1

上述查询的输出和使用JSON_ARRAYAGG(JSON_OBJECT(((生成的1功能是相同的。

Answer 1

您可以这样做：

SELECT u.user_id, u.first_name,
(SELECT JSON_ARRAYAGG(JSON_OBJECT('col1', col1, 'col2', col2, . . . ))
FROM files f
WHERE f.is_active = 1 AND f.user_id = u.user_id
) as files
FROM users u
WHERE u.user_id = 1;

也就是说，您可以从files中的列显式构造对象，然后将这些值聚合到一个数组中。

Answer 2

要根据您对@Gordon Linoff答案的评论显式键入列，您需要执行一个动态查询(PREPARED STATMENTS(。

set @qs = (SELECT
GROUP_CONCAT(COLS)
FROM (
SELECT
CONCAT(
'"', 
`COLUMN_NAME`,
'",',
`COLUMN_NAME`
) as COLS
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'test'
AND `TABLE_NAME`='Files'
) t);

set @fin = CONCAT("SELECT u.user_id, u.first_name,
(SELECT JSON_ARRAYAGG(JSON_OBJECT(",@qs,  "))
FROM Files f
WHERE f.is_active = 1 AND f.user_id = u.user_id
) as files
FROM Users u");
PREPARE dynamic_statement FROM @fin;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

请参阅此处的小提琴示例。