How to get specific json object from json array using json

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?

CodePudding user response：

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"
}