JSON_PATH_EXISTS() function no longer exists, though it did before.

Question

JSON_PATH_EXISTS() function no longer exists, though it did before.

Branwin DuBose 31

I haven't changed my query, but the JSON_VALUE_EXISTS() function no longer exists on my server. It is no recognized as a built-in function name.

Naomi Nosonovsky 8,431 Reputation points

2022-06-16T13:53:22.827+00:00

There is JSON_VALUE() function and you can also use EXISTS subquery.
Branwin DuBose 31 Reputation points

2022-06-16T13:59:43.533+00:00

Thank you! My current workaround is json_value() is not null.

I was hoping to find out why this function randomly disappeared off of my server.
Naomi Nosonovsky 8,431 Reputation points

2022-06-16T14:53:13.517+00:00

What returns by your SQL Server version? Are you using Azure?

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-path-exists-transact-sql?view=sql-server-ver16
Branwin DuBose 31 Reputation points

2022-06-16T15:03:17.043+00:00

We're using Azure. Here's what comes back from select @@version

Microsoft SQL Azure (RTM) - 12.0.2000.8 May 12 2022 23:11:24 Copyright (C) 2022 Microsoft Corporation

Accepted answer

1 additional answer

Your answer

Naomi Nosonovsky 8,431 Reputation points

2022-06-16T13:53:22.827+00:00

There is JSON_VALUE() function and you can also use EXISTS subquery.
Branwin DuBose 31 Reputation points

2022-06-16T13:59:43.533+00:00

Thank you! My current workaround is json_value() is not null.

I was hoping to find out why this function randomly disappeared off of my server.
Naomi Nosonovsky 8,431 Reputation points

2022-06-16T14:53:13.517+00:00

What returns by your SQL Server version? Are you using Azure?

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-path-exists-transact-sql?view=sql-server-ver16
Branwin DuBose 31 Reputation points

2022-06-16T15:03:17.043+00:00

We're using Azure. Here's what comes back from select @@version

Microsoft SQL Azure (RTM) - 12.0.2000.8 May 12 2022 23:11:24 Copyright (C) 2022 Microsoft Corporation

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

I tested on one of my Azure databases, and indeed, I get the error 'JSON_path_EXISTS' is not a recognized built-in function name.

I am under the impression that updates are being rolled out over a period of time. So if you have a serverless database, it could be loaded to different machines at different occasions, and these machines could have different versions of the SQL Server executable, but this does not reflect in @@version.

I don't think the compat level has anything to do with it, because a new built-in scalar function does not need protection by compat level, as the name cannot clash with a user function.

Branwin DuBose 31 Reputation points

2022-06-17T13:06:58.16+00:00

Thank you! I didn't realize that not all servers would necessarily be on the same update.

Answer 2

Tom Phillips 17,771

JSON_VALUE_EXISTS is a new feature of Azure SQL and SQL Server 2022 (in preview). This feature does not exist in prior versions of SQL Server.

Branwin DuBose 31 Reputation points

2022-06-16T16:26:36.09+00:00

By using Azure SQL I'm not automatically on that version?
The perplexing part of this to me is that the function DID work before. But today it is not found.
Tom Phillips 17,771 Reputation points

2022-06-16T16:57:26.853+00:00

Please post the results of SELECT @@VERSION.
Branwin DuBose 31 Reputation points

2022-06-16T17:04:57.337+00:00

Here's what comes back from select @@version

Microsoft SQL Azure (RTM) - 12.0.2000.8 May 12 2022 23:11:24 Copyright (C) 2022 Microsoft Corporation
Tom Phillips 17,771 Reputation points

2022-06-16T19:07:40.62+00:00

The function exists in that version. I am running the same version and it is working fine.

Are you getting an error?
Branwin DuBose 31 Reputation points

2022-06-16T19:12:08.213+00:00

Do you know if there is some way that something could have been changed to lower the language level of the server/database?

This function used to work for me, but now it does not.
Naomi Nosonovsky 8,431 Reputation points

2022-06-16T19:17:41.053+00:00

Can you check compatibility_level of your database? May be it was adjusted?
Branwin DuBose 31 Reputation points

2022-06-16T19:20:21.427+00:00

It's 150
Tom Phillips 17,771 Reputation points

2022-06-16T19:25:46.623+00:00

Are you getting an error? Or just the red line under the command in SSMS. If it is just SSMS, that is a problem with intellisense in SSMS, not SQL Server. The command still functions correctly.
Branwin DuBose 31 Reputation points

2022-06-16T19:34:12.493+00:00

Right. The error is coming from SQL Server itself. Not the IDE IntelliSense:
Tom Phillips 17,771 Reputation points

2022-06-17T13:12:22.667+00:00

That is interesting.

I have 6 AzureSQL servers. 3 of them work and 3 of them generate an error.
The ones which work report SELECT @@VERSION:
Microsoft SQL Azure (RTM) - 12.0.2000.8
Feb 23 2022 11:32:53
Copyright (C) 2021 Microsoft Corporation

The ones which do NOT work:
Microsoft SQL Azure (RTM) - 12.0.2000.8
May 12 2022 23:11:24
Copyright (C) 2022 Microsoft Corporation

I will need to ask our Azure Admin to research the version differences.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-06-17T21:11:26.283+00:00

Interesting finding, Tom! Kind of odd to remove a function...

Share via

JSON_PATH_EXISTS() function no longer exists, though it did before.

1 additional answer

Your answer