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
