时间戳

首页 > 解决方案 > 如何使用 oracle REGEXP_SUBSTR 提取特定的 json 值？

json - 如何使用 oracle REGEXP_SUBSTR 提取特定的 json 值？

问题描述

我的 Oracle 数据库中有一些包含 json 的列，为了在查询中提取它的数据，我使用REGEXP_SUBSTR.

在以下示例中，value是表DOSSIER中包含 json 的列。正则表达式提取该client.referencejson中的属性值

SELECT REGEXP_SUBSTR(value, '"client"(.*?)"reference":"([^"]+)"', 1, 1, NULL, 2) FROM DOSSIER;

因此，如果 json 看起来像这样：

[...],
"client": {
  "someproperty":"123",
  "someobject": {
    [...]
  },
  "reference":"ABCD",
  "someotherproperty":"456"
},
[...]

SQL 查询将返回ABDC.

我的问题是某些 json 有多个“客户端”实例，例如：

[...],
"contract": {
  "client":"Name of the client",
  "supplier": {
    "reference":"EFGH"
  }
},
[...],
"client": {
  "someproperty":"123",
  "someobject": {
    [...]
  },
  "reference":"ABCD",
  "someotherproperty":"456"
},
[...]

您得到了问题，现在 SQL 查询将返回EFGH，这是供应商的参考。

如何确保“引用”包含在 json 对象“客户端”中？

编辑：我在 Oracle 11g 上，所以我不能使用 JSON API，我想避免使用第三方包

标签： jsonregexoracleoracle11gregexp-substr

解决方案

假设您使用的是 Oracle 12c 或更高版本，那么您不应使用正则表达式，而应使用 Oracle 的 JSON 函数。

如果您有表格和数据：

CREATE TABLE table_name ( value CLOB CHECK ( value IS JSON ) );

INSERT INTO table_name (
  value
) VALUES (
  '{
  "contract": {
    "client":"Name of the client",
      "supplier": {
        "reference":"EFGH"
    }
  },
  "client": {
    "someproperty":"123",
    "someobject": {},
    "reference":"ABCD",
    "someotherproperty":"456"
  }
}'
);

然后你可以使用查询：

SELECT JSON_VALUE( value, '$.client.reference' ) AS reference
FROM   table_name;

哪个输出：

参考
A B C D

db<>在这里摆弄

如果您使用的是 Oracle 11 或更早版本，则可以使用第三方 PLJSON 包来解析 PL/SQL 中的 JSON。比如这个问题。

或者在数据库中启用 Java，然后使用CREATE JAVA（或loadjava实用程序）添加一个可以将 JSON 解析到数据库的 Java 类，然后将其包装在 Oracle 函数中并使用它。

推荐阅读