SQL OPENJSON Array in Objects

Question

I have a table with the following structure/rows:

ID	OptionName	OptionValue
0	Gender	Male
1	Gender	Female

I want to query the database and return the following JSON:

[{
    "OptionName":"Gender",
    "Values":[
        "Male",
        "Female"
    ]
}]

However the result I'm currently getting is this:

[{
    "OptionName":"Gender",
    "Values":[
        {
            "OptionValue":"Male"
        },
        {
            "OptionValue":"Female"
        }
    ]
}]

Here is my Query:

SELECT TOP(1) OptionName,
(
    JSON_QUERY(
        (
            SELECT OptionValue 
            FROM [TestJSON].[dbo].[Options] 
            WHERE OptionName = 'Gender' 
            FOR JSON PATH
        )
    )
) AS [Values]
FROM [TestJSON].[dbo].[Options]
WHERE OptionName = 'Gender'
FOR JSON PATH

What can I do to get the result I need?

T-SQL is a SQL dialect that is used by multiple products including Sybase, SQL Server, and Azure Synapse. What is the (R)DBMS product you are using here? [Edit] your question to tag that product, and (if relevant) the version tag for the product as well. — Thom A, Feb 08 '23 at 14:48

Zhorov · Answer 1 · 2023-02-09T07:35:40.773

Although SQL Server 2022 introduced JSON_ARRAY() function, it's difficult to use it to build a JSON array with variable items count, so you may try a string based approach:

SELECT DISTINCT o.OptionName, JSON_QUERY(a.[Values]) AS [Values]
FROM Options o
CROSS APPLY (
   SELECT CONCAT('[', STRING_AGG(CONCAT('"', OptionValue, '"'), ','), ']') 
   FROM Options
   WHERE OptionName = o.OptionName
) a ([Values]) 
--WHERE o.OptionName = 'Gender'
FOR JSON PATH

If you know the maximum number of values for each option (5 in the example), a combination of JSON_ARRAY() and PIVOT relational operator is another option:

SELECT OptionName, JSON_ARRAY([1], [2], [3], [4], [5]) AS [Values]
FROM (  
   SELECT OptionName, OptionValue, ROW_NUMBER() OVER (PARTITION BY OptionName ORDER BY ID) AS Rn
   FROM Options
) t
PIVOT (MAX(OptionValue) FOR Rn IN ([1], [2], [3], [4], [5])) p
FOR JSON PATH

SQL OPENJSON Array in Objects

1 Answers1