skip to Main Content

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


  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

    Login or Signup to reply.
  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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search