One of the built in functions - 'JSON_ARRAY' is not recognized

Question

One of the built in functions - 'JSON_ARRAY' is not recognized

Natraj 0

JSON_ARRAY is an inbuilt function available in Azure SQL Managed Instance
Ref:
https://learn.microsoft.com/en-us/sql/t-sql/functions/json-array-transact-sql?view=sql-server-ver16

But it is not supported in a Database created under SQL Managed Instance Resource

Why won't a simple query like - SELECT JSON_ARRAY(1) - won't work on this?

It gives error,
Msg 195, Level 15, State 10, Line 1

'JSON_ARRAY' is not a recognized built-in function name.

Wright, Brendan 0 Reputation points

2023-08-10T15:52:22.66+00:00

I had an issue, and they (M$ support) said the issue was with our subscription type? That our 0148G subscription doesn't have this a part of the November 2022 feature set even though we did install the setback in November 22. However, he did say "Unsupported subscription types can't be enabled individually. Other subscription types will be enrolled in upcoming months, the current ETA is September/October 2023."

As per usual it's as clear as mud, does that mean that the 0148G will or won't get it in Sept/Oct or is it in the Unsupported subscription types?

Hope this help you and others with this issue. Would be nice if a matrix of subscription and features with availabilities was offered to help clear up this confusion.

1 answer

Your answer

Wright, Brendan 0 Reputation points

2023-08-10T15:52:22.66+00:00

I had an issue, and they (M$ support) said the issue was with our subscription type? That our 0148G subscription doesn't have this a part of the November 2022 feature set even though we did install the setback in November 22. However, he did say "Unsupported subscription types can't be enabled individually. Other subscription types will be enrolled in upcoming months, the current ETA is September/October 2023."

As per usual it's as clear as mud, does that mean that the 0148G will or won't get it in Sept/Oct or is it in the Unsupported subscription types?

Hope this help you and others with this issue. Would be nice if a matrix of subscription and features with availabilities was offered to help clear up this confusion.

Answer 1

The function works in the Azure Managed Instance I have access to. But I recall that when I first got access to it, generate_series did not work, that's another new function added in SQL 2022. I was told that the instance was lagging behind in patch level, and it has since then been fixed.

So it might be something similar in your case. And this "patch level" (in lieu of a better word) is not really something that is exposed, as far as I know. I think you should open a case with Azure support to inquire.

By the way, someone may suggest that it is related to the compatibility level of the database, but it is not. I tried on my MI database to set the compat level down 100, and JSON_ARRAY was still recognised. And this is the normal state of affairs; adding a new built-in scalar function can never break anything, since user-defined scalar functions always must be accessed in two-part notation.

Share via

One of the built in functions - 'JSON_ARRAY' is not recognized

1 answer

Your answer