Hi,

I am trying to write a SQL-statement that uses json_value to extract and transform data from json.
An issue only appears when both
- a filter expression is used in the SQL-expression for more than one column (to access a specific element of an array)
- another column/expression is added, containing a json-path not targeting an array

It seems like all columns using the filter expression miracoulusly inherit the value of the very first column where a filter expression is used.

In the following example the values in column "id2" mirrors "id1". Remove the third column "someString" and "id2" suddenly gets correct data.

create table some_json (the_json clob constraint really_is_json check (the_json is json));
insert into some_json values (
'{
    "id": "a",
    "someString" : "some string",
    "externalId": [
        {
            "idType" : "1",
            "id" : "id of idType 1"
        },
        {
            "idType" : "2",
            "id" : "id of idType 2"
        }        
    ]
}'
);
insert into some_json values (
'{
    "id": "b",
    "someString" : "some string",
    "externalId": [
        {
            "idType" : "2",
            "id" : "another id of idType 2"
        },
        {
            "idType" : "1",
            "id" : "another id of idType 1"
        }
    ]
}'
);

select
    json_value(the_json, '$.someString') someString, --remove this line and it works fine
    json_value(the_json, '$.externalId[*]?(@.idType == "1").id') id1,
    json_value(the_json, '$.externalId[*]?(@.idType == "2").id') id2
from
    some_json;

Tested with Enterprise Edition 19.17.0.0.0 and 23c Free.

Am I using the filter operator wrong?

select /*+ NO_JSON_TABLE_TRANSFORM */
    json_value(the_json, '$.someString') someString, --remove this line and it works fine
    json_value(the_json, '$.externalId[*]?(@.idType == "1").id') id1,
    json_value(the_json, '$.externalId[*]?(@.idType == "2").id') id2
from
    some_json;