How can I use the oracle REGEXP_SUBSTR to extract specific json values?

Question

I have some columns in my Oracle database that contains json and to extract it's data in a query, I use REGEXP_SUBSTR.

In the following example, value is a column in the table DOSSIER that contains json. The regex extract the value of the property client.reference in that json

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

So if the json looks like this :

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

The SQL query will return ABDC.

My problem is that some json have multiple instance of "client", for example :

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

You get the issue, now the SQL query will return EFGH, which is the supplier's reference.

How can I make sure that "reference" is contained in a json object "client" ?

EDIT : I'm on Oracle 11g so I can't use the JSON API and I would like to avoid using third-party package

Answer 1

Assuming you are using Oracle 12c or later then you should NOT use regular expressions and should use Oracle's JSON functions.

If you have the table and data:

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"
  }
}'
);

Then you can use the query:

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

Which outputs:

REFERENCE
ABCD

db<>fiddle here

---

If you are using Oracle 11 or earlier then you could use the third-party PLJSON package to parse JSON in PL/SQL. For example, this question.

---

Or enable Java within the database and then use CREATE JAVA (or the loadjava utility) to add a Java class that can parse JSON to the database and then wrap it in an Oracle function and use that.