Forum Stats

  • 3,824,944 Users
  • 2,260,442 Discussions
  • 7,896,356 Comments

Discussions

Use filters in JSON_VALUE() and JSON_QUERY()

taktang
taktang Member Posts: 10
edited Sep 2, 2019 7:38AM in Database Ideas - Ideas

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

taktang
1 votes

Active · Last Updated

Comments

  • Beda Hammerschmidt-Oracle
    Beda Hammerschmidt-Oracle Member Posts: 33 Employee

    Which db version are you using?

    I tried the following in liveSql and it works today (i think version is 19.3)

    select json_query('{

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

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

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

    @}', '$.contact_numbers[*]?(@.type=="work")') from dual;

    Result:

    {

    "type" : "work",

    "value" : "01987654321"

    }

    Please note that I added a [*] to the path expression. I assume you want to select the matching item of the array and not the array itself.

  • taktang
    taktang Member Posts: 10

    Which db version are you using?

    I tried the following in liveSql and it works today (i think version is 19.3)

    select json_query('{

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

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

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

    @}', '$.contact_numbers[*]?(@.type=="work")') from dual;

    Result:

    {

    "type" : "work",

    "value" : "01987654321"

    }

    Please note that I added a [*] to the path expression. I assume you want to select the matching item of the array and not the array itself.

    I was using Oracle 18 XE.

    You're absolutely right - both portions have been implemented in 19.  I'll go download it now.

    Thanks!

    Tak