jsonregexoracle-databaseoracle11gregexp-substr

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


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


Solution

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