Difficulty Extracting Value of a Key in CLOB JSON with Unknown Path

Question

I am working with JSON data stored in a CLOB format in an Oracle database. I need to extract the value of a specific key from the JSON, but I do not know the exact path of that key. I only know the key name and want to retrieve its value regardless of its location within the JSON structure. Example JSON Here is an example of the JSON structure I am dealing with:

{
  "user": {
    "id": 1,
    "name": "John Doe"
  },
  "address": {
    "city": "New York",
    "zip": "10001"
  },
  "other": {
    "info": {
      "name": "Jane Doe"
    }
  }
}

I am trying to use functions like JSON_VALUE or JSON_QUERY to get the value of a specific key, such as the name key. However, since I do not know the exact path of this key in the JSON structure, I am having difficulty accessing it properly. I attempted to use '$..name' for searching, but I am not getting the desired result. Oracle Version: Oracle 19c

What is the best method to find the value of a specific key in JSON when the path is unknown? How should I approach extracting the value of a key in an unknown path?

Answer 1

You can use your path in a JSON_TABLE:

SELECT j.name
FROM   table_name
       CROSS APPLY JSON_TABLE(
         value,
         '$..name'
         COLUMNS (
           name VARCHAR2(4000) PATH '$'
         )
       ) j

Which, for the sample data:

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

INSERT INTO table_name (value) VALUES (
  '{
  "user": {
    "id": 1,
    "name": "John Doe"
  },
  "address": {
    "city": "New York",
    "zip": "10001"
  },
  "other": {
    "info": {
      "name": "Jane Doe"
    }
  }
}'
);

Outputs:

NAME
John Doe
Jane Doe

---

can we write this code without inserting table and with pl/sql?

Yes:

DECLARE
  v_json CLOB := '{
  "user": {
    "id": 1,
    "name": "John Doe"
  },
  "address": {
    "city": "New York",
    "zip": "10001"
  },
  "other": {
    "info": {
      "name": "Jane Doe"
    }
  }
}';
BEGIN
  FOR r IN (
    SELECT name
    FROM   JSON_TABLE(
             v_json,
             '$..name'
             COLUMNS (
               name VARCHAR2(4000) PATH '$'
             )
           )
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.name);
  END LOOP;
END;
/

Which outputs:

John Doe
Jane Doe

But you don't need PL/SQL and can just use SQL:

SELECT name
FROM   JSON_TABLE(
         '{
  "user": {
    "id": 1,
    "name": "John Doe"
  },
  "address": {
    "city": "New York",
    "zip": "10001"
  },
  "other": {
    "info": {
      "name": "Jane Doe"
    }
  }
}',
         '$..name'
         COLUMNS (
           name VARCHAR2(4000) PATH '$'
         )
       )

Oracle 21 fiddle