I'm trying to write a fairly complicated SQL Query that produces JSON as the result. All is working great except for some hardcoded arrays I need to have deeper in the hierarchy that I must use UNION ALL
to create. I've produced a query that shows my problem here (no data is required - I'm running this in Azure SQL Database):
SELECT
'Hi' AS Greeting,
(
SELECT
CASE WHEN DatePart(second, GetDate()) % 2 = 1 THEN
'qwerty'
ELSE
'asdf'
END AS Stuff
FOR JSON PATH
) AS StuffArray,
(
CASE WHEN DatePart(second, GetDate()) % 2 = 1 THEN
(
SELECT 'qwerty' AS Stuff
FOR JSON PATH
)
ELSE
(
SELECT 'asdf' AS Stuff
FOR JSON PATH
)
END
) AS QuotedStuffArray,
(
CASE WHEN DatePart(second, GetDate()) % 2 = 1 THEN
(
SELECT * FROM
(
SELECT 'qwerty' AS Stuff
UNION ALL
SELECT 'zxcvb' AS Stuff
) AS SubSelect
FOR JSON PATH
)
ELSE
(
SELECT 'asdf' AS Stuff
FOR JSON PATH
)
END
) AS WhyItMatters,
(
SELECT * FROM
(
SELECT 'qwerty' AS Stuff
UNION ALL
SELECT 'zxcvb' AS Stuff
) AS SubSelect
FOR JSON PATH
) AS ButThisIsFine
FOR JSON PATH
This outputs this JSON:
[
{
"Greeting": "Hi",
"StuffArray": [
{
"Stuff": "qwerty"
}
],
"QuotedStuffArray": "[{\"Stuff\":\"qwerty\"}]",
"WhyItMatters": "[{\"Stuff\":\"qwerty\"},{\"Stuff\":\"zxcvb\"}]",
"ButThisIsFine": [
{
"Stuff": "qwerty"
},
{
"Stuff": "zxcvb"
}
]
}
]
In this query, you'll see four different objects in the hierarchy beyond the base object: StuffArray
, QuotedStuffArray
, WhyItMatters
, and ButThisIsFine
. The StuffArray
object is exactly what I want all of my objects to look like - pure JSON without anything escaped. However, when I begin to put my SELECT
syntax inside of my CASE
statements, my results begin to be quoted, as shown by the QuotedStuffArray
object. So for the first two objects, this is fine. But I have a problem where I sometimes need to do a conditional UNION
of two hardcoded values which forces me to put my SELECT
into the CASE
statement as shown by the WhyItMatters
object. The ButThisIsFine
object produces the output formated like I want the WhyItMatters
object to be formatted but it removes the conditional UNION
, which I need.
How can I get this last WhyItMatters
object to produce pure JSON without escaped quotes just like the ButThisIsFine
object while keeping in that conditional UNION
statement?
JSON_QUERY()
around theSELECT
(rather than plain parentheses) achieve what you want?