How to do Where clause on simple Json Array in SQL Server 2017?

Question

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

  {"pages":["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')

Edit:

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'

instead of in, have you tried using like ? I I think it gives the instances where we have Page 1 references (without even using Json_query), also with Json_query are you trying to extract the value there or the JSON format in the select list ? — Avi
– Avi, Commented Jan 3, 2019 at 0:45

David Browne - Microsoft · Accepted Answer · 2019-01-03 00:31:10Z

4

Something like this:

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

returns

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

(2 rows affected)

answered Jan 3, 2019 at 0:31

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

1 Comment

chobo2 Over a year ago

ok, I will try that. I also came up with above from examples I found on the MS docs, though I think your seems simpler.

Collectives™ on Stack Overflow

How to do Where clause on simple Json Array in SQL Server 2017?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related