create table some_json (the_json clob constraint really_is_json check (the_json is json))Table created.
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"
}
]
}'
)1 row(s) inserted.
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"
}
]
}'
)1 row(s) inserted.
COMMITStatement processed.
select
json_value(the_json, '$.someString') someString,
json_value(the_json, '$.externalId[*]?(@.idType == "1").id') id1,
json_value(the_json, '$.externalId[*]?(@.idType == "2").id') id2
from
some_json| SOMESTRING | ID1 | ID2 | some string | id of idType 1 | id of idType 1 | some string | another id of idType 1 | another id of idType 1 |
|---|
select
--json_value(the_json, '$.someString') someString,
json_value(the_json, '$.externalId[*]?(@.idType == "1").id') id1,
json_value(the_json, '$.externalId[*]?(@.idType == "2").id') id2
from
some_json| ID1 | ID2 | id of idType 1 | id of idType 2 | another id of idType 1 | another id of idType 2 |
|---|