2

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

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.

Improve this question
2
  • 1
    That's a good question. I don't actually know of a direct way to do that. For very simple examples, you might be able to do some string manipulation (very carefully). Although you're probably better off running your JSON through some other processing layer if you need empty arrays rendered (e.g python, jq, js, etc.). Although I'd love to be proved wrong too! MSSQL's JSON support is "good enough" in my mind, but not super mature. There are definitely things that you can do when manipulating JSON in general which you can't do in SQL specifically. Commented Nov 10, 2023 at 15:59
  • Not an empty array [], but FROM sys.tables t FOR JSON PATH,INCLUDE_NULL_VALUES will return "indexes": null Commented Nov 10, 2023 at 16:02

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

3 Comments

Why the nested select '[]'
@Charlieface Shot in the dark to get desired empty array.
Just do a normal '[]' by itself?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.