SQL Server中如何将JSON数组外键转为逗号分隔的产品名称列表？

假设SQL Server数据库中有以下两张表：

products表 #

users表 #

需要创建名为report的SQL视图，将users表中的likedProductsIds转换为对应的产品名称逗号分隔列表，预期结果如下：

report视图预期结果 #

作为SQL新手，已知需要用OPENJSON反序列化JSON数组、STRING_AGG聚合产品名称，但不知道如何结合两者完成查询。

可以通过子查询+聚合函数的方式，将OPENJSON解析出的产品ID与products表关联，再用STRING_AGG拼接名称，最后和users表关联得到结果，这种写法更简洁直观。

创建视图的SQL语句 #

CREATE VIEW report AS
SELECT
    u.id,
    u.user_name AS name,
    (
        SELECT STRING_AGG(p.product_name, ', ')
        FROM OPENJSON(u.likedProductsIds) WITH (productId INT '$') AS j
        JOIN products p ON j.productId = p.id
    ) AS likedProductNames
FROM users u;

语句说明 #

1. 解析JSON数组：OPENJSON(u.likedProductsIds) WITH (productId INT '$')会把JSON数组中的每个ID解析成单独一行，列名为productId并转为INT类型。
2. 关联产品表：将解析出的productId与products表的id匹配，获取对应的产品名称。
3. 聚合拼接名称：STRING_AGG函数把同一个用户的所有产品名称用, 拼接成一个字符串。
4. 自动处理NULL：如果likedProductsIds为NULL，子查询会直接返回NULL，完全符合预期结果。

如果偏好CTE（公用表表达式）的写法，逻辑一致，只是需要额外用UNION ALL补充无喜欢产品的用户数据：

CREATE VIEW report AS
WITH UserLikedProducts AS (
    SELECT
        u.id,
        u.user_name,
        p.product_name
    FROM users u
    CROSS APPLY OPENJSON(u.likedProductsIds) WITH (productId INT '$') j
    JOIN products p ON j.productId = p.id
)
SELECT
    id,
    user_name AS name,
    STRING_AGG(product_name, ', ') AS likedProductNames
FROM UserLikedProducts
GROUP BY id, user_name
UNION ALL
SELECT
    id,
    user_name AS name,
    NULL AS likedProductNames
FROM users u
WHERE u.likedProductsIds IS NULL;

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