1

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).

Zhorov
  • 28,486
  • 6
  • 27
  • 52
SJ1
  • 344
  • 3
  • 12

1 Answers1

2

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
Zhorov
  • 28,486
  • 6
  • 27
  • 52