Microsoft SQL Server Finding rows that contain value in the JSON array


In this example, Tags array may contain various keywords like ["promo", "sales"], so we can open this array and filter values:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON will open inner collection of tags and return it as table. Then we can filter results by some value in the table.