CODEWITHSUNDEEP
arraysjsont-sql
Mark M
Mark M

Reputation: 39

T-SQL parsing JSON containing a dynamic array of values

In T-SQL, parsing JSON and dealing with arrays in a dynamic fashion it seems clear, one needs to use the OPENJSON table-value function in order to deal with the fact there is no awareness of the indexes available. If this array is a simple array of values:

SELECT *
FROM OPENJSON(N'["element1","element2","element3"]')

it renders with an element per row as one would expect; perfect for counting the rows and iterating the values. However I need to then perform this operation in reverse and build an array of values. To test I thought something like this might work:

SELECT RTRIM([value])
FROM OPENJSON(N'["element1","element2","element3"]')
ORDER BY [key] FOR JSON PATH

but I get the error:

Msg 13605, Level 16, State 1, Line 8 Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.

I've tried dabbling in various forms of the OPENJSON function and the JSON_QUERY function to no avail. Eventually I did arrive at this "solution":

SELECT REPLACE(REPLACE(REPLACE(CATEND,'},{"V":',','),'[{"V":','['),'}]',']')
FROM (
    SELECT (
        SELECT [value] AS V
        FROM OPENJSON(N'["element1","element2","element3"]')
        ORDER BY [key] FOR JSON PATH
    ) AS CATEND
) T

but this does not sit well with me. My focus and the point of using the JSON library functions is to avoid text parsing and having to include the costly REPLACE function feels like it shouldn't be necessary. Am I missing something?!

Upvotes: 2

Views: 1119

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

Regrettfully the developers forgot to add something like AS ARRAY hint.

OPENJSON() can read a naked array, where the [key] is the element's position and the [value] is the item, but we cannot create such an array with JSON functionality:

There are some workarounds:

--Create a mockup to simulate your issue:

DECLARE @tbl1 TABLE(ID INT IDENTITY,SomeValue VARCHAR(100));
INSERT INTO @tbl1 VALUES('Row 1'),('Row 2');

DECLARE @tbl2 TABLE(ID1 INT,SomeDetail VARCHAR(100));
INSERT INTO @tbl2 VALUES(1,'Det 1.1'),(1,'Det 1.2')
                       ,(2,'Det 2.1'),(2,'Det 2.2.'),(2,'Det 2.3');

--Using AUTO mode will detect the JOIN and create an array of objects.
--This is close, but you won't get the unnamed array:

SELECT *
FROM @tbl1 t1
INNER JOIN @tbl2 t2 ON t1.ID=t2.ID1
FOR JSON AUTO;

/*
    {
        "ID": 1,
        "SomeValue": "Row 1",
        "t2": [
            {
                "ID1": 1,
                "SomeDetail": "Det 1.1"
            },
            {
                "ID1": 1,
                "SomeDetail": "Det 1.2"
            }
        ]
    }
*/

--Using PATH mode will return each combination - not what you need

SELECT *
FROM @tbl1 t1
INNER JOIN @tbl2 t2 ON t1.ID=t2.ID1
FOR JSON PATH;

/*
    {
        "ID": 1,
        "SomeValue": "Row 1",
        "ID1": 1,
        "SomeDetail": "Det 1.1"
    }
*/

--Using a correlated sub-query will lead to the same result as the AUTO mode above

SELECT *
      ,(
        SELECT *
        FROM @tbl2 t2 
        WHERE t2.ID1=t1.ID --<-- correlated sub-query 
        FOR JSON PATH
       ) AS Details
FROM @tbl1 t1
FOR JSON PATH;

--So we can use the correlated sub-query together with a XML-hack for string-concatenation:

SELECT *
      ,JSON_QUERY
      (
           CONCAT('['
          ,STUFF((
            SELECT CONCAT(',"',t2.SomeDetail,'"') 
            FROM @tbl2 t2 
            WHERE t2.ID1=t1.ID --<-- correlated sub-query 
            FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,1,''),']')
      ) AS Details
FROM @tbl1 t1
FOR JSON PATH;

--Finally this is what you want:

/*
    {
        "ID": 1,
        "SomeValue": "Row 1",
        "Details": [
            "Det 1.1",
            "Det 1.2"
        ]
    }
*/

If you have v2017+ you are lucky, because you can use STRING_AGG():

SELECT t1.ID
      ,JSON_QUERY(CONCAT('[',STRING_AGG(CONCAT('"',t2.SomeDetail,'"'),','),']')) AS Details
FROM @tbl1 t1
INNER JOIN @tbl2 t2 ON t1.ID=t2.ID1
GROUP BY t1.ID
FOR JSON PATH;

For the moment we have to wait for a future version to bring this really needed feature natively...

Upvotes: 4

Related Questions