How to have JSON subquery return empty array rather than null when there are no rows

Question

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 unfortunatly in reality if there are no indexes, the indexes array won't be omitted, 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 array of results, rather than no array of results, if there are no results, when running a query FOR JSON?

---

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 i can i make JSON return an empty something?

Bonus Reading

• BOL: Format Query Results as JSON with FOR JSON
• How to force SQL Server to return empty JSON array (returns a string rather than an array)
• How to generate empty JSON array in SQL Server? (returns a string rather than an array)
• How to return empty JSON array from SQL Server (returns a string rather than an array)

Edit: For those who voted to close as a duplicate; you completely missed the fact that specifically mentioned that question you linked, and rejected it already. So, please don't close questions when you don't know what you're talking about.

Answer 1

If you are on SQL Server 2022+ then you can wrap your subquery in an ISNULL and then return an empty JSON array with JSON_ARRAY:

SELECT t.Name,
       t.object_id,
       t.type_desc,
       t.create_date,
       ISNULL((SELECT i.name,
                      i.type_desc,
                      i.is_unique,
                      i.is_primary_key
               FROM sys.indexes i
               WHERE i.object_id = t.object_id
               AND type <> 0
               FOR JSON PATH, INCLUDE_NULL_VALUES),JSON_ARRAY()) indexes
FROM sys.tables t
FOR JSON PATH, INCLUDE_NULL_VALUES;

If you're not in 2022+, then you could just use '[]', which does work as well.

In master this resulted in:

[
    {
        "Name": "spt_fallback_db",
        "object_id": 117575457,
        "type_desc": "USER_TABLE",
        "create_date": "2003-04-08T09:18:01.557",
        "indexes": []
    },
    {
        "Name": "spt_fallback_dev",
        "object_id": 133575514,
        "type_desc": "USER_TABLE",
        "create_date": "2003-04-08T09:18:02.870",
        "indexes": []
    },
    {
        "Name": "spt_fallback_usg",
        "object_id": 149575571,
        "type_desc": "USER_TABLE",
        "create_date": "2003-04-08T09:18:04.180",
        "indexes": []
    },
    {
        "Name": "spt_monitor",
        "object_id": 580913141,
        "type_desc": "USER_TABLE",
        "create_date": "2023-10-16T16:35:22.277",
        "indexes": []
    },
    {
        "Name": "MSreplication_options",
        "object_id": 2107154552,
        "type_desc": "USER_TABLE",
        "create_date": "2019-06-19T13:20:55.087",
        "indexes": []
    }
]

db<>fiddles: 2019, 2022

Answer 2

If <2022, here is another option. Not as pretty as Thom A's +1 :)

SELECT 
    t.Name,
    t.object_id,
    t.type_desc,
    t.create_date,
    ( json_query(
        coalesce( 
        (
        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) 
        ,( select '[]') )
        )
     ) indexes
FROM sys.tables t FOR JSON PATH,INCLUDE_NULL_VALUES