Forum Stats

  • 3,874,599 Users
  • 2,266,752 Discussions
  • 7,911,910 Comments

Discussions

Use filters in JSON_VALUE() and JSON_QUERY()

taktang
taktang Member Posts: 10 Blue Ribbon
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: 34 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 Blue Ribbon

    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

  • Bancha
    Bancha Member Posts: 17 Green Ribbon
    edited Jul 22, 2022 7:50AM

    CREATE TABLE employee

    (id number NOT NULL,

    CONSTRAINT employee_pk PRIMARY KEY,

    employee_doc CLOB,

    CONSTRAINT employee_doc_ensure_json CHECK (employee_doc IS JSON (STRICT WITH UNIQUE KEYS)));


    INSERT INTO employee

    VALUES (1,

    '{"EmployeeId": 100,

    "Age": 40,

    "Gender" : "M"}');


    INSERT INTO employee

    VALUES (2,

    '{"EmployeeId": 101,

    "Age": 40,

    "Gender" : ""}');


    INSERT INTO employee

    VALUES (3,

    '{"EmployeeId": 102,

    "Age": 40}');


    INSERT INTO employee

    VALUES (4,

    '{"EmployeeId": 103,

    "Age": 40,

    "Gender" : "F"}');


    SELECT *

       FROM employee

       WHERE JSON_EXISTS(employee_doc,'$?( @.Age == "40" && @.gender == 'M')')

    I am getting first record correctly.

    But now my requirement is gender should be consider as optional. If gender tag is there in json then it should consider otherwise not.

    Example: employee 103 should not populate as gender is F. All other 3 record should populate is my expectation.

    My expectation is all 3 record should display.Can someone help me for the logic

    Using oracle 19c