How to get specific json object from json array using json_query in oracle

February 16, 2022

For example, I have a json array like below

[
{ id: 1, name: "larry" },    
{ id: 2, name: "curly" },
{ id: 3, name: "moe" }
]

How can I get the json object which name is curly using json_query function?

>Solution :

The answer depends on your Oracle version, which you did not include. (Always include your database version, especially in questions about JSON!)

In Oracle 19 (I think – perhaps also in Oracle 18) you can use JSON_QUERY with a path expression with a predicate, something like this:

with
  sample_data (j_arr) as (
    select '
      [
        { id: 1, name: "larry" },    
        { id: 2, name: "curly" },
        { id: 3, name: "moe" }
      ]
    ' from dual
  )
select json_query(j_arr, '$[*]?(@.name == "curly")' returning varchar2) as curly
from   sample_data
;

CURLY
-----------------
{
"id" : 2,
"name" : "curly"
}