时间戳

首页 > 解决方案 > 如何从 SQL 2016 中 FOR JSON PATH 的结果中删除密钥

sql - 如何从 SQL 2016 中 FOR JSON PATH 的结果中删除密钥

问题描述

由于许多不同的原因，我们已经开始在我们的 SQL 代码中使用 FOR JSON PATH with。在这种情况下，尽管我遇到了一些问题。我正在尝试将 OrderItemIds 列表放入单个 JSON 字符串中。但它产生的是具有单个属性的对象数组 - > {“OrderItemId”：“00000000-0000-0000-0000-000000000000”}。我的目标是生成一个仅包含 Guid 值而不是其键的 json 字符串。


SELECT 
    OrderItemId 
FROM 
    OrderItems 
FOR JSON PATH

预期的：

"["00000000-0000-0000-0000-000000000000","00000000-0000-0000-0000-000000000000"]"

实际的：

"[{"OrderItemId":"00000000-0000-0000-0000-000000000000"},{"OrderItemId":"00000000-0000-0000-0000-000000000000"}]"

尝试1：（成功，但只是部分）

CONCAT(
    '[',
    SUBSTRING(
        (
            SELECT ',"'+ST1.[value]+'"'  AS [text()]
            FROM @table ST1            
            FOR XML PATH ('')
        ), 2, 9999),
    ']')

结果：所以这一点代码完全按照我希望我的结果工作的方式工作。这是使用 FOR XML PATH 而不是 JSON PATH，这当然很好，因为它产生了我想要的东西。但是这段代码在我想要的地方折腾和使用有点令人生畏。所以我们想为什么不把它放在一个函数中，我们可以将值传递给它。

尝试2：（再次成功，但只是部分）制作自定义类型和功能

CREATE TYPE ValueList
AS TABLE ([value] [nvarchar](100) NULL)

CREATE FUNCTION ConvertToValueList(@table ValueList READONLY)
RETURNS NVARCHAR(max)
AS BEGIN
RETURN CONCAT(
            '[',
            SUBSTRING(
                (
                    SELECT ',"'+ST1.[value]+'"'  AS [text()]
                    FROM @table ST1            
                    FOR XML PATH ('')
                ), 2, 9999),
            ']')

使用示例：

DECLARE 
    @OrderItemIds ValueList;

INSERT INTO @OrderItemIds 
(
    [value]
)
SELECT 
    [OrderItemId] 
FROM 
    [dbo].[OrderItems]

SELECT [dbo].[ConvertToValueList](@OrderItemIds)

结果：这最终完全按计划工作。我现在遇到的问题是当我想在视图中使用该功能时。我不能，因为我需要声明值列表才能传递给函数。

结束说明：所以现在我只是使用尝试 1 中的 Concat 语句，直到我们能够提出更好的解决方案。

标签： sqljsonsql-server

解决方案

使用 XML 路径：

DECLARE @OrderItems TABLE (OrderItemId UniqueIdentifier PRIMARY KEY)

INSERT INTO @OrderItems 
    VALUES ( NEWID () )
         , ( NEWID () )
         , ( NEWID () )
         , ( NEWID () )
         , ( NEWID () )

SELECT   CONCAT (STUFF ( (  SELECT  ',' +QUOTENAME (CONVERT (VARCHAR(36), OrderItemId ),'"')
                            FROM    @OrderItems 
                            FOR XML PATH (''), TYPE 
                         ). value('text()[1]','varchar(max)')
                       , 1, 1, '"['
                       )
                ,']"'
                )
;

结果：

"["135B908B-E5FD-4658-B69C-4E380509581B","4EEDF234-167D-4141-8542-A0173482BFD6","53901E4C-0486-44D5-A0AB-C6BC2FAE39B1","A263D28A-0948-461D-BC46-F9A6D167E37F","5795D046-098E-4AFB-9B2C-FB3DC56F6F31"]"

推荐阅读