ORACLE - JSON到键值对表 [英] ORACLE - JSON To Key Value Pair Table

问题描述

有没有办法从CLOB Json Column中获取包含键/值对的表？

Is there any way to obtain a table with key/value pairs from a CLOB Json Column?

这里的想法是以动态的方式获取这些值。因为CLOB列并不总是包含相同的结构。

The idea here is to get these values, on a dynamic way. Because the CLOB column does not always contain the same structure.

我创建了一个这样做的函数，但是因为它实际上解析了json字符串，所以当我们使用它时在一张有很多记录的表中，它非常缓慢。而且非常慢，我的意思是每秒2-5条记录，我知道这很糟糕。

I've created a function that does this, however since it literally parses the json string, when we use it in a table with many records its very slow. And by very slow I mean like 2-5 records per second, i know it's terrible.

Oracle工具（v.12c）不提供动态获取方式json标签/值，我们一直指定路径。

The Oracle tools (v.12c) do not provide a dynamic way to obtain the json tags/values, we have always to specify the paths.

我一直在四处挖掘，没有任何运气。有什么想法吗？

I've been digging all around without any luck. Any thoughts?

推荐答案

SQL> create or replace type NV_PAIR_T as object (
  2    NAME  VARCHAR2(32),
  3    VALUE VARCHAR2(32)
  4  )
  5  /

Type created.

SQL> create or replace type NV_PAIR_TABLE as TABLE of NV_PAIR_T
  2  /

Type created.

SQL> create or replace function GET_KEY_VALUES(P_JSON_DOC VARCHAR2)
  2  return NV_PAIR_TABLE PIPELINED
  3  as
  4    JO JSON_OBJECT_T := JSON_OBJECT_T(P_JSON_DOC);
  5    JO_KEYS JSON_KEY_LIST := JO.get_keys();
  6  begin
  7
  8    for i in 1..JO_KEYS.count loop
  9      pipe row (NV_PAIR_T(JO_KEYS(i),JO.get_string(JO_KEYS(i))));
 10    end loop;
 11  end;
 12  /

Function created.

SQL> select *
  2   from TABLE(GET_KEY_VALUES('{"A":"AA", "B":"BB", "C":"CC"}'))
  3  /
A                                AA
B                                BB
C                                CC

SQL>

这是否有帮助

这篇关于ORACLE - JSON到键值对表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！