如何将usersPermissions数据转为JSON数组关联至users表查询？

针对你的需求（查询所有用户，同时将每个用户对应的权限记录以JSON数组形式返回），根据不同数据库类型，提供以下可行的SQL写法：

1. MySQL（5.7及以上版本） #

使用JSON_ARRAYAGG聚合JSON对象为数组，JSON_OBJECT将单条权限记录转为JSON格式：

SELECT
    u.*,
    IFNULL(
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', p.id,
                'userId', p.userId,
                'permissionId', p.permissionId
            )
        ),
        JSON_ARRAY()
    ) AS permissions
FROM users u
LEFT JOIN usersPermissions p ON u.id = p.userId
GROUP BY u.id, u.firstName, u.lastName, u.email, u.phone -- 需包含users表所有非聚合字段（严格模式要求）
ORDER BY u.id;

• LEFT JOIN确保无权限的用户也能被查询到，IFNULL将无权限时的NULL转为空JSON数组[]

2. PostgreSQL（9.4及以上版本） #

使用json_agg聚合数组，json_build_object构造单条权限的JSON对象：

SELECT
    u.*,
    COALESCE(
        json_agg(
            json_build_object(
                'id', p.id,
                'userId', p.userId,
                'permissionId', p.permissionId
            )
        ),
        '[]'::json
    ) AS permissions
FROM users u
LEFT JOIN usersPermissions p ON u.id = p.userId
GROUP BY u.id -- 主键分组时无需额外指定其他字段
ORDER BY u.id;

• COALESCE用于将无权限时的NULL转为空JSON数组

3. SQL Server（2016及以上版本） #

通过子查询结合FOR JSON PATH生成JSON数组：

SELECT
    u.*,
    ISNULL(
        (
            SELECT
                p.id,
                p.userId,
                p.permissionId
            FROM usersPermissions p
            WHERE p.userId = u.id
            FOR JSON PATH
        ),
        '[]'
    ) AS permissions
FROM users u
ORDER BY u.id;

• FOR JSON PATH自动将子查询结果转为JSON数组，ISNULL处理无权限的空值场景

关于你之前尝试的问题说明 #

• 第一个伪代码直接返回p.*作为子查询结果，数据库不允许子查询返回多行数据作为单列值，因此执行报错。
• ChatGPT生成的JSON_OBJECTAGG是用于生成键值对形式的JSON对象，而非数组，且参数格式不符合要求（该函数仅接受键、值两个参数），因此无法得到你需要的数组结构。

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