SQL server OPENJSON with multiple where on a filed

Question

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

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?

When you say you want 2 products, what do you mean? In your data above you have three products (Product1, 2 and 4) matching your criteria: red or blue and brand 1. If that was a typo then you can quite easily achieve this with a CTE and in the SELECT clause you join to yourself. I have that code here, but I just wanted to clarify first about those two products. — Niels Berglund
– Niels Berglund, Commented Aug 19, 2017 at 16:05
@NielsBerglund I know there's 3 product, but i just want the first two product that matching the criteria. It's paging. I know i must use OFFSET but that's not working in this issue — Masoud
– Masoud, Commented Aug 19, 2017 at 19:05

Niels Berglund · Accepted Answer · 2017-08-20 05:37:59Z

1

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.

answered Aug 20, 2017 at 5:37

Niels Berglund

1,70810 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Masoud Over a year ago

thanks for your answer, it's work. but it's not return the first two product, it's return the first two record from join and both of them are Product 1

Niels Berglund Over a year ago

With due respect it's hard to answer when you change the requirements as you go along. Perhaps if you asked the question is such a way that it clearly stated what you want to know, and perhaps also include an example of the result you want, based on the JSON data you posted in the question. That would make it a lot easier to come up with the answer you want.

Masoud Over a year ago

you helped me a lot, thanks. just a question, can you give me a tip for that pagination problem?

Niels Berglund Over a year ago

For pagination, use the code in my answer. You should probably wrap it in a stored procedure, so you can send in parameter values for offset and row to fetch.

Collectives™ on Stack Overflow

SQL server OPENJSON with multiple where on a filed

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related