Query SQL database with JSON Value

Question

Here is my JSON:

[{"Key":"schedulerItemType","Value":"schedule"},{"Key":"scheduleId","Value":"82"},{"Key":"scheduleEventId","Value":"-1"},{"Key":"scheduleTypeId","Value":"2"},{"Key":"scheduleName","Value":"Fixed Schedule"},{"Key":"moduleId","Value":"5"}]

I want to query the database by FileMetadata column

I've tried this:

  SELECT * FROM FileSystemItems WHERE JSON_VALUE(FileMetadata, '$.Key') = 'scheduleId' and JSON_VALUE(FileMetadata, '$.Value') = '82'

but it doesn't work!

I had it working with just a dictionary key/value pair, but I needed to return the data differently, so I am adding it with key and value into the json now.

What am I doing wrong?

AlwaysLearning · Accepted Answer · 2020-12-19 05:11:55Z

1

With the sample data given you'd have to supply an array index to query the 1th element (0-based array indexes), e.g.:

 select *
 from dbo.FileSystemItems
 where json_value(FileMetadata, '$[1].Key') = 'scheduleId'
 and json_value(FileMetadata, '$[1].Value') = '82'

If the scheduleId key can appear at arbitrary positions in the array then you can restructure the query to use OPENJSON instead, e.g.:

select *
from dbo.FileSystemItems
cross apply openjson(FileMetadata) with (
  [Key] nvarchar(50) N'$.Key',
  Value nvarchar(50) N'$.Value'
) j
where j.[Key] = N'scheduleId'
and j.Value = N'82'

answered Dec 19, 2020 at 5:11

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

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

1 Comment

user272727 Over a year ago

That was it!!! Thank you :-) I used the first item. It should always be at the 1 index (2nd item)

Collectives™ on Stack Overflow

Query SQL database with JSON Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related