Where clause on json data in Sql Server 2016

Question

I have a nvarchar(1000) field in my table and I am storing JSON data in that column.

eg :

 CONTENT_RULE_ID    CONTENT_RULE
 1                  {"EntityType":"Inquiry", "Values":[1,2]}
 2                  {"EntityType":"Inquiry", "Values":[1,3]}
 3                  {"EntityType":"Inquiry", "Values":[2,4]}
 4                  {"EntityType":"Inquiry", "Values":[5,6,1]}
 6                  {"EntityType":"Inquiry", "Values":[8,1]}
 8                  {"EntityType":"Inquiry", "Values":[10,12,11]}

from this how can I get all the CONTENT_RULE_ID which is having inquiry id 1 using JSON_QUERY in sql server

score 4 · Answer 1 · answered Jan 12 '18 at 11:24

4

SELECT c.*
FROM CONTENT_RULES AS c
CROSS APPLY OPENJSON(JSON_QUERY(content_rule, '$')) AS x 
CROSS APPLY OPENJSON(x.[Value], '$') AS y
where x.[key]='Values' and y.[value]=1

answered Jan 12 '18 at 11:24

Harisyam M

407
1
3
14

Eralper · Accepted Answer · 2018-08-10T12:22:00.430

2

@Harisyam, could you please try following query

declare @val int = 1

;with cte as (
    select *
    from CONTENT_RULES
    cross apply openjson (CONTENT_RULE, '$')
), list as (
    select 
    CONTENT_RULE_ID, replace(replace([value],'[',''),']','') as [value]
    from cte 
    where CONTENT_RULE_ID in (
    select CONTENT_RULE_ID
    from cte 
    where [key] = 'EntityType' and [value] = 'Inquiry'
    ) 
    and [key] = 'Values'
)
select 
CONTENT_RULE_ID, s.value
from list
cross apply string_split([value],',') s
where s.value = @val

I used SQL string_split function to get inquiry values one by one

output is

A second query can be following one

select
    CONTENT_RULE_ID
from CONTENT_RULES
cross apply openjson (CONTENT_RULE, '$')
where replace(replace(value,'[',','),']',',') like '%,1,%'

And maybe the most complete SQL query which requires OpenJSON support is as follows

select
    content_rule_id,
    [value]
from Content as c
cross apply openjson(c.CONTENT_RULE, '$') with (
    EntityType nvarchar(100),
    [Values] nvarchar(max) as json
) as e
cross apply openjson([Values], '$') as v

edited Aug 10 '18 at 12:22

answered Jan 12 '18 at 10:41

Eralper

6,461
2
21
27

Hi @Erapler, Thanks for your help. Your solution worked as expected. But is there any easy way to do this using any inbuilt function, similar to IN statement in normal sql query – Harisyam M Jan 12 '18 at 10:52
And what about your SQL Server version? Is it supporting OpenJSON ? I guess so since you seemed to test it – Eralper Jan 12 '18 at 10:55
Yes it supports OpenJSON since I am using SQL Server 2016 – Harisyam M Jan 12 '18 at 10:58
I added another one into the answer – Eralper Jan 12 '18 at 11:02
This is simple and working. I found another way without string replace function – Harisyam M Jan 12 '18 at 11:23

score 1 · Answer 3 · answered Oct 27 '21 at 05:46

My case is similar but instead of an integer array, mine is an array of complex type. Here is my code based on David Browne's solution

SELECT *
FROM TableName AS T
WHERE EXISTS
(
    SELECT *
    FROM OPENJSON(T.JsonColumn, '$.Details')
    WITH
    (
        OrderNumber VARCHAR(200) '$.OrderNumber',
        Quantity INT '$.Quantity'
    )
    WHERE OrderNumber = '1234567'
);

In your case:

SELECT C.*
FROM CONTENT_RULES AS C
WHERE EXISTS
(
    SELECT *
    FROM OPENJSON(C.CONTENT_RULE, '$.Values')
    WHERE value = 1
);

Gerard H. Pille · Answer 4 · 2018-01-12T11:04:02.907

0

sql server 2016 can open JSON.

Try this:

SELECT c.content_rule_ID, y.[key], y.[value]
  FROM content_rules AS c
  CROSS APPLY OPENJSON(JSON_QUERY(content_rule, '$.content_rule')) AS x
  CROSS APPLY OPENJSON(x.[Values], '$') AS y
  where y.[value] = 1
    and x.[EntityType] = 'Inquiry';

edited Jan 12 '18 at 11:04

answered Jan 12 '18 at 10:47

Gerard H. Pille

2,528
1
13
17

Thanks for the help. This solution is not working. But from your answer, I got the idea to do this. – Harisyam M Jan 12 '18 at 11:22
SELECT c.* FROM CONTENT_RULES AS c CROSS APPLY OPENJSON(JSON_QUERY(content_rule, '$')) AS x CROSS APPLY OPENJSON(x.[Value], '$') AS y where x.[key]='Values' and y.[value]=1 – Harisyam M Jan 12 '18 at 11:35
Also added as an answer – Harisyam M Jan 12 '18 at 11:35

Where clause on json data in Sql Server 2016

4 Answers4