Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Use filters in JSON_VALUE() and JSON_QUERY()

taktangAug 30 2019 — edited Sep 2 2019

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

Comments
Post Details
Added on Aug 30 2019
3 comments
1,248 views