如何在 SQL Server 2017 中对简单的 Json 数组执行 Where 子句? [英] How to do Where clause on simple Json Array in SQL Server 2017?

问题描述

假设我的数据库中有一个名为 attributes 的列，它以该值为例:

Say I have a column in my database called attributes which has this value as an example:

  {"pages":["Page1"]}

如何使用 where 子句来过滤包含Page1"的行.

How can I do a where clause so I can filter down rows that have "Page1" in it.

  select JSON_QUERY(Attributes, '$.pages') 
  from Table
  where JSON_QUERY(Attributes, '$.pages') in ('Page1')

来自 docs 看起来这可能有效，尽管它所做的事情看起来很复杂.

From the docs it seems like this might work though it seems so complicated for what it is doing.

  select count(*)
  from T c
  cross apply Openjson(c.Attributes)
              with (pages nvarchar(max) '$.pages' as json) 
  outer apply openjson(pages) 
              with ([page] nvarchar(100) '$')
  where [page] = 'Page1'

推荐答案

是这样的:

use tempdb
create table T(id int, Attributes nvarchar(max))

insert into T(id,Attributes) values (1, '{"pages":["Page1"]}')
insert into T(id,Attributes) values (2, '{"pages":["Page3","Page4"]}')
insert into T(id,Attributes) values (3, '{"pages":["Page3","Page1"]}')

select *
from T
where exists
( 
  select * 
  from openjson(T.Attributes,'$.pages') 
  where value = 'Page1'
)

返回

id          Attributes
----------- ---------------------------
1           {"pages":["Page1"]}
3           {"pages":["Page3","Page1"]}

(2 rows affected)

这篇关于如何在 SQL Server 2017 中对简单的 Json 数组执行 Where 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！