Passing dynamic key in JSON_VALUE() in Oracle

Question

I have a club type column which has Json data. I have around 100 keys which are unique to each record. we are using json_value('json_data', 'keyname'). In my case i don't know actual for this record. So i need to join with another table where there keys are defined. In place of keyname i want to pass columnname. Its giving some error saying :

Syntax error,
Expecting: string

So can any one suggest, how to get data from a json column by passing a dynamic key in runtime.

Lets say I have two table table_1 and table_2. Table_1 has column called json_data_column which stores data in json format. Table_1 has the FK to TABLE_2 which has mapping key. so we have to find out what is value of that dynamic key of each record.

If I am giving any static String in place of t2.json_key , then its working. But when giving the dynamic values, it's not working.

WORKING

select
       json_value ( json_value (t1.json_data_column, '$.string'), '$.my_key' )
from TABLE_1 t1
       inner join TABLE_2 t2 on t1.json_key_fk = t2.id

NOT WORKING

select
       json_value ( json_value (t1.json_data_column, '$.string'), t2.json_key )
from TABLE_1 t1
       inner join TABLE_2 t2 on t1.json_key_fk = t2.id

Dataset:

{"string":"{\"id\":133100,\"data_found\":5,\"isActive\":\"true\",\"process\":\"completed\",\"status\":\"COMPLETED\"}"}

{"string":"{\"id\":133100,\"data_found\":5,\"isDelete\":\"true\",\"process\":\"completed\",\"status\":\"COMPLETED\"}"}

{"string":"{\"id\":133100,\"data_found\":5,\"isUnderProgress\":\"false\",\"process\":\"completed\",\"status\":\"COMPLETED\"}"}

{"string":"{\"id\":133100,\"data_found\":5,\"isSentToClient\":\"false\",\"process\":\"completed\",\"status\":\"COMPLETED\"}"}

Answer 1

What you are trying to do cannot be supported directly by the architecture. Basically we set up a JSON PATH engine that searches each row in the table (document in the collection) for a set of path that is defined at statement compile time. In your case you are expecting us to work with a path that cannot be known until the row is retrieved.

in 12.2 (which is now GA) we can use PL/SQL JSON features to do this..

SQL> set lines 120 pages 0
SQL> with FUNCTION GET_NAME(P_JSON_DOC in VARCHAR2) RETURN VARCHAR2
  2  is
  3  begin
  4    return SUBSTR(P_JSON_DOC,INSTR(P_JSON_DOC,'"',1,5)+1,INSTR(P_JSON_DOC,'"',1,6)-INSTR(P_JSON_DOC,'"',1,5)-1);
  5  end;
  6  FUNCTION GET_KEY_VALUE(P_JSON_DOC VARCHAR2, P_KEY VARCHAR2) RETURN VARCHAR2
  7  is
  8    JO JSON_OBJECT_T;
  9  begin
 10    JO := JSON_OBJECT_T(P_JSON_DOC);
 11    return JO.get_STRING(P_KEY);
 12  end;
 13  MY_TABLE as (
 14    select COLUMN_VALUE JSON_DOC
 15      from TABLE(
 16             XDB$STRING_LIST_T(
 17               '{"string":"{\"id\":133100,\"data_found\":5,\"isActive\":\"true\",\"process\":\"completed\",\"status\"
:\"COMPLETED\"}"}',
 18               '{"string":"{\"id\":133100,\"data_found\":5,\"isDelete\":\"true\",\"process\":\"completed\",\"status\"
:\"COMPLETED\"}"}',
 19               '{"string":"{\"id\":133100,\"data_found\":5,\"isUnderProgress\":\"false\",\"process\":\"completed\",\"
status\":\"COMPLETED\"}"}',
 20               '{"string":"{\"id\":133100,\"data_found\":5,\"isSentToClient\":\"false\",\"process\":\"completed\",\"s
tatus\":\"COMPLETED\"}"}'
 21             )
 22           )
 23  )
 24  select GET_NAME(EMBEDDED_JSON),GET_KEY_VALUE(EMBEDDED_JSON,GET_NAME(EMBEDDED_JSON))
 25    from (
 26           select JSON_VALUE(JSON_DOC,'$.string') EMBEDDED_JSON
 27             from MY_TABLE
 28         )
 29  /
isActive
true

isDelete
true

isUnderProgress
false

isSentToClient
false


SQL>

In 12.1 the GET_NAME function can use EXECUTE IMMEDIATE

FUNCTION GET_KEY_VALUE(P_JSON_DOC VARCHAR2, P_KEY VARCHAR2) RETURN VARCHAR2
is
  V_RESULT VARCHAR2(200);
begin
  EXECUTE IMMEDIATE 'select JSON_VALUE(:1,''$.' || P_KEY || ''') from dual' into V_RESULT using P_JSON_DOC;
  return V_RESULT;
end;