sql - 动态选择 json_table 的列
问题描述
我有这个查询:
DECLARE
rc sys_refcursor;
j_keys varchar2(2000);
query_s varchar2(20000);
BEGIN
j_keys := '(
SELECT
listagg(distinct k.COLUMN_VALUE || '' varchar(256) PATH ''$.'' || k.COLUMN_VALUE, '', '') as j_cols
FROM (select json_response as json_value from SOME_TABLE where param=''some_param'') t
CROSS APPLY JSON_TABLE(
t.json_value,
''$[*]''
COLUMNS (
idx FOR ORDINALITY,
json_obj VARCHAR2(4000) FORMAT JSON PATH ''$''
)
) jt
CROSS APPLY get_keys( jt.json_obj ) k
)';
query_s := 'SELECT * FROM json_table((select json_response from SOME_TABLE where param=''some_param''), ''$[*]''
COLUMNS
' || j_keys || ')';
open rc for query_s;
dbms_sql.return_result(rc);
END;
这是一个讨厌的查询,旨在测试为 json_table 动态选择列的可能性(然后解析所选 clob 中的任何 json-string - 在 SOME_TABLE 中命名为 json_response)
不完全确定我的语法设置正确,但目前它抱怨:
ORA-00904: invalid identifier
在第 22 行(“打开 rc for '...' 行)
解决方案
您希望运行第一个查询,而不是创建包含查询文本的字符串文字,然后将第一个查询的输出放入第二个查询字符串:
DECLARE
rc sys_refcursor;
j_keys varchar2(2000);
query_s varchar2(20000);
BEGIN
SELECT listagg(
k.COLUMN_VALUE || ' varchar(256) PATH ''$.' || k.COLUMN_VALUE || '''',
','
)
INTO j_keys
FROM ( SELECT JSON_QUERY( json_value, '$[1]' RETURNING CLOB) AS json_obj
FROM table_name
)t
CROSS APPLY get_keys( t.json_obj ) k;
query_s := 'SELECT jt.*
FROM table_name t
CROSS APPLY JSON_TABLE(
t.json_value,
''$[*]''
COLUMNS
' || j_keys || ') jt';
open rc for query_s;
DECLARE
col1 VARCHAR2(50);
col2 VARCHAR2(50);
col3 VARCHAR2(50);
BEGIN
LOOP
FETCH rc INTO col1, col2, col3;
EXIT WHEN rc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( col1 || ', ' || col2 || ', ' || col3 );
END LOOP;
END;
-- or
-- dbms_sql.return_result(rc);
END;
/
其中,鉴于此设置:
CREATE TABLE table_name (
id NUMBER
GENERATED ALWAYS AS IDENTITY
PRIMARY KEY,
json_value CLOB
CHECK( json_value IS JSON )
);
INSERT INTO table_name ( json_value ) VALUES (
'[{"column1":"value1","column2":"value2","column3":"value3"},
{"column1":"value4","column2":"value5","column3":"value6"},
{"column3":"value9","column1":"value7","column2":"value8"}]'
);
CREATE FUNCTION get_keys(
value IN CLOB
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
keys JSON_KEY_LIST;
BEGIN
keys := js.get_keys();
FOR i in 1 .. keys.COUNT LOOP
PIPE ROW ( keys(i) );
END LOOP;
END;
/
CREATE FUNCTION get_value(
value IN CLOB,
path IN VARCHAR2
) RETURN VARCHAR2
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
BEGIN
RETURN js.get_string( path );
END;
/
输出:
value1, value2, value3 value4, value5, value6 value7, value8, value9
db<>在这里摆弄
推荐阅读
- html - 将 CSS 类添加到所有标题 HTML 元素?
- python - 无法在 python 3.9 中安装 pyaudio 如何安装
- hyperledger-fabric - 无法将 bash 脚本中的调用命令输出捕获为变量
- python - 在两个不同长度的 2d numpy 数组之间计算每行的元素匹配
- javascript - 如何计算两周选择者之间的周数
- spring-boot - 在 Heroku 上绑定 Spring Boot 端口失败
- user-interface - 机器人从 whatspp 聊天中打开 ZOOM URL
- javascript - 定义一个类可能的属性
- numpy - 如何分配值以具有指定的概率密度函数
- sql - 从多对多自引用实体中获取子实体