I have a (hypothetical) JSON schema that calls for a document to look like:
[
{
"tableName": "Embeddings",
"object_id": 1301579675,
"type_desc": "USER_TABLE",
"create_date": "2023-05-03T19:41:56.940",
"indexes": [
{
"name": "PK_Embeddings",
"type_desc": "CLUSTERED",
"is_unique": true,
"is_primary_key": true
},
{
"name": "IX_Embeddings_DimValue",
"type_desc": "NONCLUSTERED",
"is_unique": false,
"is_primary_key": false
}
]
}
]
Which you can generate using the query:
SELECT
t.Name,
t.object_id,
t.type_desc,
t.create_date,
(
SELECT name, type_desc, is_unique, is_primary_key
FROM sys.indexes si
WHERE si.object_id = t.object_id
AND type <> 0
FOR JSON PATH) indexes
FROM sys.tables t
FOR JSON PATH
You’ll notice the indexes array.
- if there are indexes, they appear in the array.
- If there are no indexes, i want the array to be empty.
But unfortunately in reality, if the list of indexes is empty the indexes
array won’t be empty, but instead be omitted:
{
"Name": "DateTests",
"object_id": 901578250,
"type_desc": "USER_TABLE",
"create_date": "2020-01-20T10:56:27.757"
}
when it should return:
{
"Name": "DateTests",
"object_id": 901578250,
"type_desc": "USER_TABLE",
"create_date": "2020-01-20T10:56:27.757",
"indexes": []
}
How can I instruct SQL Server to return an empty of array of results, rather than no array of results, if there are no results, when running a query FOR JSON
?
Bonus Chatter
It’s odd that it returns nothing, rather than an "empty array", because SQL loves to return an empty set of something:
SELECT name, type_desc, is_unique, is_primary_key FROM sys.indexes WHERE 0=1
name type_desc is_unique is_primary_key 0 row(s) affected
It doesn’t return nothing, it returns an empty something.
So how can I make JSON return an empty something?
2
Answers
If you are on SQL Server 2022+ then you can wrap your subquery in an
ISNULL
and then return an empty JSON array withJSON_ARRAY
:If you’re not in 2022+, then you could just use
'[]'
, which does work as well.In
master
this resulted in:db<>fiddles: 2019, 2022
If <2022, here is another option. Not as pretty as Thom A’s +1 🙂