json - SQL - How to speed up a query execution while using JSON_Value in WHERE clause - Stack Overflow

admin管理员组

文章数量:1336576

So, we have a SQL table named Clients which has the following columns:

1. UniqueId (nvarchar(200), not null)
2. Data (nvarchar(max), null) - this one has JSON for each row(aprox. 30k-40k characters)

And we got a request to return to our user, UniqueId using the value from one of the parameters nested in Data JSON field (let's call it Value in this example)

We got something like this:

select UniqueId
from dbo.Clients 
where JSON_VALUE(Data,'$.Parent.Child.ChildOfChild.Value') like 'Value'

But, as expected, table which has 20k+ rows and each row contains JSON in Data(with 30k-40k characters) takes a very long time to execute.

Our question is, is there any way to speed up this kind of query? Given the fact that we can't change the table design. Thanks in advance!

本文标签： jsonSQLHow to speed up a query execution while using JSONValue in WHERE clauseStack Overflow