MSSQL JSON_VALUE to match ANY Object in Array

Question

I have a table with a JSON text field:

create table breaches(breach_id int, detail text);
insert into breaches values
( 1,'[{"breachedState": null}, 
      {"breachedState": "PROCESS_APPLICATION",}]')

I'm trying to use MSSQL's in build JSON parsing functions to test whether ANY object in a JSON array has a matching member value.

If the detail field was a single JSON object, I could use:

select * from breaches 
 where JSON_VALUE(detail,'$.breachedState') = 'PROCESS_APPLICATION'

but it's an Array, and I want to know if ANY Object has breachedState = 'PROCESS_APPLICATION'

Is this possible using MSSQL's JSON functions?

Bochen Lin · Accepted Answer · 2017-08-14 23:11:29Z

1

You can use function OPENJSON to check each object, try this query:

select * from breaches 
where exists
(
    select * 
    from
    OPENJSON (detail) d
    where JSON_VALUE(value,'$.breachedState') = 'PROCESS_APPLICATION'
)

Btw, there is an extra "," in your insert query, it should be:

insert into breaches values
( 1,'[{"breachedState": null}, 
      {"breachedState": "PROCESS_APPLICATION"}]')

answered Aug 14, 2017 at 23:11

Bochen Lin

4435 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

MSSQL JSON_VALUE to match ANY Object in Array

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related