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

cwtwac6a 于 2023-04-05 发布在 Oracle

关注(0)|答案(2)|浏览(367)

我的Oracle数据库中有一些列包含json，为了在查询中提取它的数据，我使用REGEXP_SUBSTR。
在下面的示例中，value是表DOSSIER中包含json的列。正则表达式提取该json中属性client.reference的值

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

如果json看起来像这样：

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

SQL查询将返回ABDC。
我的问题是一些json有多个“client”示例，例如：

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

您得到了问题，现在SQL查询将返回EFGH，这是供应商的引用。
如何确保“reference”包含在json对象“client”中？
编辑：我在Oracle 11g上，所以我不能使用JSON API，我想避免使用第三方软件包

oracle

来源：https://stackoverflow.com/questions/68254208/how-can-i-use-the-oracle-regexp-substr-to-extract-specific-json-values

2条答案

按热度按时间

kqqjbcuj1#

假设你使用的是Oracle 12 c或更高版本，那么你应该不使用正则表达式，而应该使用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;

其输出：
| 参考文献|
| --------------|
| ABCD语言|

db〈〉小提琴here *

如果您使用的是Oracle 11或更早版本，则可以使用第三方PLJSON包来解析PL/SQL中的JSON。
或者在数据库中启用Java，然后使用CREATE JAVA（或loadjava实用程序）添加一个可以解析JSON的Java类到数据库中，然后将其 Package 在Oracle函数中并使用它。

赞(0）回复(0）举报 2023-04-05

bq3bfh9z2#

我最近遇到了类似的问题。如果“reference”是一个只存在于“client”对象内部的属性，这将解决：

SELECT reference FROM (
    SELECT DISTINCT 
        REGEXP_SUBSTR(
            DBMS_LOB.SUBSTR(
                value,
                4000
            ),
            '"reference":"(.+?)"',
            1, 1, 'c', 1) reference
    FROM DOSSIER
) WHERE reference IS NOT null;

您也可以尝试调整正则表达式以满足您的需要。
编辑：
在我的例子中，列类型是CLOB，这就是为什么我在那里使用DBMS_LOB.SUBSTR函数。您可以删除此函数并直接在REGEXP_SUBSTR中传递列。

赞(0）回复(0）举报 2023-04-05

我来回答

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

2条答案

相关问题

热门标签

最新问答