我在MySQL中搜索"FOR JSON PATH“(SQL Server)等效项,发现了以下链接- stackoverflow question
评级最高的解决方案可以完美地工作,但是否可以动态提供列和值?我有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相同。
发布于 2021-06-14 18:27:42
你可以这样做:
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中的列显式构造对象,然后将这些值聚合到一个数组中。
发布于 2021-06-14 21:26:13
为此,您需要执行一个动态查询(PREPARED STATMENTS),显式地按照your comment on @Gordon Linoff's answer键入列。
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;https://stackoverflow.com/questions/67968377
复制相似问题