I have a table with single column containing JSON type objects (column type nvarchar) and have a requirement of filtering rows.
JSON object is an array of objects containing multiple fields, Is there a way I can apply between clause on each value of array and return that row if it matches.
ROW_1 = [{"key": 12}, {"key": 13}, {"key": 19}]
For the above string if between clause has range between 15 to 22, then this row should be selected.
There are two challenges I see in above requirement, 1 is to use wild_cards to select all keys and other to apply between clause on each value.
Similar use-case, is to select a row if it matches the value for which I did something like below
select *
from table
where CAST(JSON_QUERY(column, '$'), nvarchar(max)) LIKE '%"key": 12%'
Let me know if this can be done using a T-SQL query.
PS. Other alternatives include loading all data using python and filter there. (but my concern is that I would need to load complete data every time which might slowdown the filtering due to increase in number of rows in future).
You may use an APPLY operator and an OPENJSON() call to parse the stored JSON and apply the appropriate WHERE clause:
SELECT *
FROM (VALUES (N'[{"key": 12}, {"key": 13}, {"key": 19}]')) v (JsonData)
CROSS APPLY OPENJSON(v.JsonData) WITH ([key] int '$.key') j
WHERE j.[key] BETWEEN 5 AND 12