代码之家 › 专栏 › 技术社区 › Harisyam M

Sql Server 2016中json数据的Where子句

sql-server-json json-query sql-server-2016 sql-server json

Harisyam M · 技术社区 · 8 年前

我的表中有一个nvarchar(1000)字段,我正在该列中存储JSON数据。

如:

 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]}

因此,如何在sql server中使用JSON\u查询获取查询ID为1的所有CONTENT\u RULE\u ID

4 回复 | 直到 8 年前

Harisyam M 8 年前

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

Eralper 7 年前

@Harisyam,你能试试下面的查询吗

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

我使用了SQL string_split function 逐个获取查询值

输出为

第二个查询可以在一个查询之后

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

可能需要OpenJSON支持的最完整的SQL查询如下

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

walterhuang 4 年前

我的情况类似,但不是整数数组,而是复杂类型的数组。这是我的代码基于 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'
);

在您的情况下:

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

Gerard H. Pille 8 年前

sql server 2016可以打开JSON。

尝试以下操作:

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';