时间戳

首页 > 解决方案 > 用于读取嵌套 JSON 数据的 Oracle 查询

oracle - 用于读取嵌套 JSON 数据的 Oracle 查询

问题描述

我有一个表，其中有一列包含我想读取的嵌套 JSON 数据以获取查询结果。但是列的数据类型是 VARCHAR，里面的数据是一个 JSON 字符串，里面有嵌套的对象。

现在，当我点击下面的查询时，它可以正常工作并给我结果，

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

下面是 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\"}
]}"
}

上面的数据是单个元数据列中的所有字符串，我将其拆分只是为了使其更具可读性。

但我也想根据“deliveryStatus”内容过滤数据。所以当我尝试下面的查询时，

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

它不起作用。不显示任何结果。虽然没有错误。我觉得我需要一些其他方法来读取该字符串中嵌套的 JSON 内容。但我不知道该怎么做。

有人可以提供有关如何实现这一目标的任何见解吗？

标签： oracleoracle-sqldeveloper

解决方案

反斜杠是正则表达式中的转义字符，因此您必须使用第二个反斜杠对其进行转义。

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

但如果您使用的是 Oracle 12c 或更高版本，我确实建议您研究原生 JSON 支持，就像@thatjeffsmith 提到的那样。正则表达式有效，但它们既昂贵又脆弱。

推荐阅读