在Oracle中的JSON_VALUE()中传递动态密钥 [英] Passing dynamic key in JSON_VALUE() in Oracle

问题描述

我有一个俱乐部类型的列，其中包含Json数据. 我有大约100个键，每个记录都是唯一的. 我们正在使用json_value('json_data'，'keyname'). 就我而言，我不知道这张唱片的真实情况.所以我需要加入另一个定义了键的表.我想通过键名来代替键名. 它给出了一些错误信息:

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

任何人都可以建议如何通过在运行时传递动态密钥来从json列中获取数据.

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

假设我有两个表table_1和table_2. 表_1具有名为json_data_column的列，该列以json格式存储数据. 表_1具有到具有映射键的TABLE_2的FK. 因此我们必须找出每条记录的动态键的价值.

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.

如果我用任何静态String代替t2.json_key，那么它将正常工作. 但是当提供动态值时，它不起作用.

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.

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

不工作

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

数据集:

{"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\"}"}

推荐答案

该体系结构无法直接支持您尝试执行的操作.基本上，我们设置了一个JSON PATH引擎，该引擎搜索表中的每一行(集合中的文档)以查找在语句编译时定义的一组路径.对于您而言，您希望我们使用的路径只有在检索到该行后才能知道.

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.

在12.2(现在为GA)中，我们可以使用PL/SQL JSON功能来做到这一点.

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>

在12.1版本中，GET_NAME函数可以使用EXECUTE IMMEDIATE

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;

这篇关于在Oracle中的JSON_VALUE()中传递动态密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！