Oracle query to read nested JSON data

Question

I have a table which has a column with nested JSON data that I want to read for a query result. But the data type of column is VARCHAR and data inside is a JSON string with nested objects inside.

Now when I hit below query it works fine and gives me result,

select * from dataTable where regexp_like(metadata,'(*)"id":"33001"(*)');

Below is the metadata column of dataTable :

{"id":"33001",
"digits":"1234",
"requestId":"5d54-f6-48-8d-8155190",
"deliveryMethod":"ATT",
"messageStatus":"{\"status\":[
{\"tokenId\":\"Zktx\",\"deliveryStatus\":\"SUCCESS\",\"code\":\"0\"},
{\"tokenId\":\"aGsx\",\"deliveryStatus\":\"SUCCESS\",\"code\":\"0\"}
]}"
}

Above data is all String in single metadata column, I have split it just so that it is more readable.

But I also want to filter the data based on "deliveryStatus" contents. So when I try below query,

select * from dataTable where regexp_like(metadata,'(*)"id":"33001"(*)') AND regexp_like(metadata,'(*)\"deliveryStatus\":\"SUCCESS\"(*)');

It doesn't work. Does NOT show any result. There is no error though. I feel I need some other approach to read the nested JSON contents inside that string. But I am not sure how to do that.

Can someone provide any insights of how to achieve this?

what version of Oracle are you on? We have native JSON support now that would make your query pretty easy, no need to for regex at all. — thatjeffsmith
– thatjeffsmith, Commented Jun 12, 2019 at 12:06
Actually I tried looking into that but the tables are already created with me having no access and also we are not on Oracle 12c or up yet. But thanks for your response. That would have worked if we had that support. — WebNoob
– WebNoob, Commented Jun 13, 2019 at 9:18

kfinity · Accepted Answer · 2019-06-12 13:04:21Z

1

Backslash is an escape character in regex, so you have to escape it with a second backslash.

-- sample data
with datatable as (select '{"id":"33001",
"digits":"1234",
"requestId":"5d54-f6-48-8d-8155190",
"deliveryMethod":"ATT",
"messageStatus":"{\"status\":[
{\"tokenId\":\"Zktx\",\"deliveryStatus\":\"SUCCESS\",\"code\":\"0\"},
{\"tokenId\":\"aGsx\",\"deliveryStatus\":\"SUCCESS\",\"code\":\"0\"}
]}"
}' metadata from dual)
-- actual query
select * from dataTable where regexp_like(metadata,'(*)"id":"33001"(*)') 
    AND regexp_like(metadata,'(*)\\"deliveryStatus\\":\\"SUCCESS\\"(*)'); -- note double backslashes

But I do recommend looking into the native JSON support if you're on Oracle 12c or up, like @thatjeffsmith mentioned. Regex work, but they're expensive and fragile.

answered Jun 12, 2019 at 13:04

kfinity

9,1411 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WebNoob Over a year ago

It indeed was the double backslash issue. Thanks for pointing out. Problem solved now.

Collectives™ on Stack Overflow

Oracle query to read nested JSON data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related