在sql查询中，我希望将一列输出为子值的json数组

9o685dep 于 2023-02-20 发布在其他

关注(0)|答案(1)|浏览(162)

我有一个产品表，我在其中查询了各种字段。还有一个ProductTagMapping表，它为每个产品分配了几个标记。我想向输出行添加一个Tags列，其中包含ProductTag表中关联标记名称的JSON数组。因此，结果将是：

ProductId: 0
ProductName: Pretty Necklace
Tags: ["gold", "topaz", "fire"]

通过连接，我将为每个标签获得许多相同的产品行：

SELECT p.ProductId, p.ProductName. pt.TagName
FROM Product AS p
INNER JOIN ProductTagMapping as ptm ON ptm.ProductId = p.ProductId
INNER JOIN ProductTag as pt ON pt.TagId = ptm.TagId

我找到了一个近似的答案，但它并不正确：

SELECT      ROW_NUMBER() OVER (ORDER BY p.SKU) AS Id,
        p.Id AS ProductId,
        p.Name AS ProductName,
        REPLACE(REPLACE((SELECT
                pt.Name as TagNames
                FROM
                ProductTag AS pt
                INNER JOIN
                    dbo.Product_ProductTag_Mapping as ptm ON ptm.Product_Id = p.Id AND ptm.ProductTag_Id = pt.Id                        
                WHERE
                ptm.Product_Id = p.Id
                ORDER BY
                pt.Name
            FOR
                JSON AUTO
            ), N'{"pt.Name":', N''), '"}', '"') AS [Tags]

来自dbo.产品组件p，带（无锁定）
其结果为：

"productid": 8,
"productName": "Gold Tone Earrings",
"tags": "[{\"TagNames\":\"blue\",{\"TagNames\":\"clipon\",{\"TagNames\":\"gold\"]"

如何正确创建标签列？谢谢Abbott

JSON

来源：https://stackoverflow.com/questions/75492691/in-a-sql-query-i-want-to-output-one-column-as-a-json-array-of-child-values

1条答案

按热度按时间

8i9zcol21#

你的代码看起来不错，但是你需要在你的“replace”字符串上做一个JSON_QUERY，否则它会把它变成一个你不想要的字符串。看看我下面的版本来了解我的意思。你也替换了错误的单词，你可能会说：REPLACE（...，“标记名”，"“）。
如果您使用的是不支持创建数组或STRING_AGGs的旧版SQL Server，则需要执行以下操作。2022支持直接创建数组：

create table #products (productid int, productname nvarchar(100))
create table #producttag (productid int, tagid int)
create table #tag (tagid int, tagname nvarchar(100))

insert into #products
values  (1, 'Earring')
,   (2, 'Blouse')

insert into #tag
values  (1, 'Gold')
,   (2, 'Metal')
,   (3, 'Blue')
,   (4, 'Red')

insert into #producttag
VALUES  (1, 1)
,   (1, 2)
,   (1, 3)
,   (2, 4)

select productid, productname,
  JSON_QUERY(stuff((select ',"' + STRING_ESCAPE(tagname, 'json') + '"'
   FROM #producttag pt
   INNER JOIN #tag t
      ON t.tagid = pt.tagid
   WHERE pt.productid = p.productid
   for xml path(''), type
   ).value('.', 'nvarchar(max)'), 1, 1, '[') + ']') tags
from #products p
for json path

我不喜欢replaces，所以我使用xml path手动创建数组字符串，然后将其转换为json数组。

赞(0）回复(0）举报 2023-02-20

我来回答

在sql查询中，我希望将一列输出为子值的json数组

1条答案

相关问题

热门标签

最新问答