How to remove the key from the result of FOR JSON PATH in SQL 2016

Question

We have begun to use FOR JSON PATH with in our SQL code for many different reasons. In this scenario though I'm having a little bit of issues with it. I am trying to get a list of OrderItemIds into a single JSON string. But what it produces is an array of objects with a single Property -> {"OrderItemId": "00000000-0000-0000-0000-000000000000"}. My goal is to produce a json string with just the values of the Guids and not their key.


SELECT 
    OrderItemId 
FROM 
    OrderItems 
FOR JSON PATH

Expected:

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

Actual:

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

Attempt 1: (Successful, but only in part)

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

Results: So this little bit of code works exactly how I would want my result to work. This is using FOR XML PATH instead of JSON PATH, which is fine of course because it produces what I want. But this bit of code is kind of daunting to toss and use all over the place where I want it. So we thought why not stick it into a function and we can pass the values to it.

Attempt 2: (AGAIN successful, but only in part) Make a custom type and function

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),
            ']')

Usage example:

DECLARE 
    @OrderItemIds ValueList;

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

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

Results: This ends up working exactly as planned. The issue I run into now is when I am wanting to use that function in a View. I can't because I need to declare the value list in order to pass into the function.

End Note: So for now I am just using the Concat statement from attempt 1 until we can come up with a better solution.

Try this approch https://stackoverflow.com/a/3672860/764369 – Arooran Nov 27 '19 at 11:20 — Arooran, Nov 27 '19 at 11:20

score 0 · Answer 1 · answered Aug 10 '19 at 12:23

with XML PATH:

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, '"['
                       )
                ,']"'
                )
;

Result:

"["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"]"

If you can see in my examples this one of solutions we've tried. We just created our own type and made a function. I basically want that to be reusable inside of a view. — Cody W Hageman, Aug 10 '19 at 14:43

How to remove the key from the result of FOR JSON PATH in SQL 2016

1 Answers1