Oracle - Filter JSON array while using json

In my PL/SQL code I'm receiving a JSON array:

[
   {"name": "Tony", " age": "20", "city": "Bigville"},
   {"name": "Lucas", "age": "40", "city": "Smallville"},
   {"name": "Mike",  "age": "40", "city": "Otherville"}
]

Then I use this code to generate an object like this: {"Tony": "20", "Lucas": "40"}:

   select json_objectagg(key "name" value "age") into v_patch
   from(
      with t(dta) as ( select json_query(:data, '$') from dual )
      select "name", "age" from t
      cross join json_table(dta,
         '$' columns(nested path '$[*]'
            columns(
               "name" varchar2(1024) path '$.name',
               "age"  varchar2(1024) path '$.age'
            )
         )
      )
   );

How can I filter the array above so that only persons from Bigville or Smallville are added to the final object?

CodePudding user response：

If you don't want to change your code very much, you can just add a filter to your JSON path:

select json_objectagg(key "name" value "age") into v_patch
from(
  with t(dta) as ( select json_query(:data, '$') from dual )
  select "name", "age" from t
  cross join json_table(dta,
     '$' columns(nested path '$[*]?(@.city in ("Bigville","Smallville"))'
        columns(
           "name" varchar2(1024) path '$.name',
           "age"  varchar2(1024) path '$.age'
        )
     )
  )
);

CodePudding user response：

A couple of oddities in your data: Tony doesn't have an "age" key but he does have " age" (with a leading space); hopefully that is just a typo. And the "age" should be numeric, not a string; JSON supports the number data type, why not use it?

A couple of oddities in your existing code: there is no need to use json_query as you did, json_table accepts string inputs (clob, varchar2) directly. And there is no need for a nested path clause - there are no nested objects in your data.

An oddity in the output: "names" are all entities "of the same kind", so why do they become distinct properties of the output object? It would seem more natural to ask for a json array as output. It would help us if we could understand why you are doing things this way. As an aside, if there are two or more people with the same name, you will create an object with repeated keys; do you understand what issues that may lead to?

Fixing all these various things, your query can be adapted - and simplified - to something like this:

select json_objectagg(key "name" value "age") into v_patch
from   json_table(:data, '$[*]?(@.city in ("Bigville", "Smallville"))'
         columns( "name" varchar2 path '$.name',
                  "age"  varchar2 path '$.age' )
       )
;

Note that path expressions with predicates (like the filter by city) weren't supported in early versions (12.1, 12.2). At worst, you can extract the city from the input data as well, and use a where clause as shown in astenix's answer.

CodePudding user response：

You may add city column as output and use general where clause to restrict by city.

with a as (
  select q'([
   {"name": "Tony", "age": "20", "city": "Bigville"},
   {"name": "Lucas", "age": "40", "city": "Smallville"},
   {"name": "Mike",  "age": "40", "city": "Otherville"}
])' as j from dual
)
select
  json_objectagg(
    key js.name
    value js.age
  ) as res
from a
  cross join json_table(
    a.j
    , '$[*]'
    columns (
      name varchar2(10)
      , age int
      , city varchar2(20)
    )
  ) js
where js.city in ('Bigville', 'Smallville')

| RES                        |
| :------------------------- |
| {"Tony":20, "Lucas":40} |

db<>fiddle here

Also please note that quoted identifiers are case-sensitive and should not be used if possible.