Consider removing unnecessary CASTs around JSON_VALUE

[roji]

We currently generate JSON queries such as the following:

SELECT [a].[Id], [a].[Name], JSON_QUERY([a].[Contact],'$')
FROM [Authors] AS [a]
WHERE CAST(JSON_VALUE([a].[Contact],'$.Address.City') AS nvarchar(max)) = N'Chigley'

We seem to systematically wrap JSON_VALUE with a CAST to the specific type - we should consider whether this is actually necessary and when.

Aside from the general verbosness/ugliness factor and unknown perf implications, I expect users will just follow the SQL Server documentation for JSON indexes, which instruct users to create a virtual computed column with the JSON_VALUE expression, and create an index over that. I expect lots of users won't know to include the CAST inside their compuetd column, leading to the index not getting used.

[roji]

FYI, if the user doesn't include the cast in an index computed column, this causes SQL Server to do an index scan instead of a seek, which causes a very significant perf degradation. We should really prioritize this (and possibly even consider patching, if it's really low-risk). See https://github.com/roji/JsonColumnsPerf for more details.

[ErikEJ]

And you cannot create indexes on nvarchar(MAX) columns...

[ErikEJ]

Re-open to consider for current?

[ajcvickers]

I may have drunk a glass or two of wine, but fuck it, let's patch this!

[ErikEJ]

That will make a lot of DBAs happy and Reduce carbon footprint 😅

[ajcvickers]

Rejected for servicing: "It's only perf on a new feature (not a regression) and no customers have reported an impact on their business."

[ErikEJ]

Too bad. Assume it is in 8.0 today?

[ajcvickers]

Yes.