Scenario

You have a JSON document which contains an array of objects.  You want to select the object(s) where one of the fields meets a specific criteria.

Example

I have a set of contact numbers for a person.

{

  "contact_numbers" : [ { "type" : "home",   "value" : "01234567890" },

                        { "type" : "mobile", "value" : "07891234567" },

                        { "type" : "work",   "value" : "01987654321" } ]

}

I'd like to retrieve the object containing the work phone number, with something like this :-

SELECT JSON_QUERY(payload, '$.contact_numbers?(@.type=="work")') AS work_contact

The highlighted section is the filter expression.

Currently, you can use a filter expression in JSON_EXISTS(), but you can't use it in JSON_VALUE() or JSON_QUERY().

It would also be useful to be able to follow the filter expression with more steps, such as ".value".

SELECT JSON_QUERY(payload, '$.contact_numbers?(@.type=="work").value') AS work_number