CODEWITHSUNDEEP
sql-serverjsont-sqlsql-server-2016
Masoud
Masoud

Reputation: 111

SQL server OPENJSON with multiple where on a filed

I have a table with a json field. The json schema is the same for all records.

enter image description here

I want to get just 2 products with red or blue color and with brand 1.
I tried the below query but I know that's not working:

SELECT [Id], [JName], [JValue]
FROM   [Product]
CROSS APPLY OPENJSON([Json])
WITH ([JName] NVARCHAR(50) '$.name', [JValue] NVARCHAR(50) '$.value')
WHERE 
    (CASE WHEN [JName]=N'color' AND [JValue] IN (N'red', N'red') THEN 1 ELSE 0 END) &
    (CASE WHEN [JName]=N'brand' AND [JValue] IN (N'brand 1') THEN 1 ELSE 0 END) = 1

so, how should I write this query?

Upvotes: 0

Views: 2508

Answers (1)

Niels Berglund
Niels Berglund

Reputation: 1698

I am a bit unsure of what you are asking:

How to get data matching your criteria (red or blue and brand1), since your posted query will not get what you want.

OR

How to do an OFFSET for pagination purposes.

OR

Both.

Anyway, after my initial comment above (but before your reply), I wrote a query which would give you what you want (red or blue and Brand1).

After your reply I modified the query to do OFFSET as well:

;WITH prod
AS
(
  SELECT [Id], [JName], [JValue]
  FROM   dbo.tb_Product
  CROSS APPLY OPENJSON([Json])
  WITH ([JName] NVARCHAR(50) '$.name', [JValue] NVARCHAR(50) '$.value')
  )
SELECT p1.Id, p1.JValue AS Color, p2.JValue AS Brand
FROM prod p1
JOIN prod p2
  ON p1.Id = p2.Id
WHERE p1.JName = 'Color'
  AND p1.JValue IN ('red', 'blue')
  AND p2.JName = 'Brand'
  AND p2.JValue IN ('Brand1')
ORDER BY p1.ID
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY;

I hope this is somewhat what you want.

Upvotes: 1

Related Questions