JSON_TABLE을 사용하여 Oracle JSON 열에서 Key Value를 결과 집합으로 가져오는 방법

JSON_TABLE을 사용하여 Oracle JSON 열에서 Key Value를 결과 집합으로 가져오는 방법

구글 검색을 많이 했는데 간단한 사용 사례에 대한 간단한 해결책을 찾을 수 없는 것 같습니다.저는 오라클 12C 데이터베이스에 json 열을 가지고 있고(물론 json 제약 조건이 있는 varchar), 그 열에 이렇게 맵의 표현을 저장합니다.

{
"a":9.0847,
"b":859.947
}

plsql에서 다음과 같은 결과 집합을 반환하고 싶습니다.

key val
a   9.0847
b   859.947

저는 아래와 같이 무한대로 변형된 것으로 보이며, 모든 예는 제 사용 사례에 비해 너무 작위적입니다.

select b.* from mytable a,json_table(myJsonCol,'$'
columns ( value varchar2(500) path '$.myjsonkey')) b

이 경우 값 목록만 반환되며, 해당 키는 반환되지 않습니다.json 데이터는 항상 문자열 이중 키 값입니다.

감사해요.

EDIT 좀 더 문맥을 추가하기 위해, 저는 지금 이것을 하기 위해 포스트그레스에서 json_each를 사용하고, 오라클에서 비슷한 방법을 찾고 있습니다.

다음은 SQL 내에서 PL/SQL API를 사용하는 18c와 21c에서 시도한 일반적인 솔루션입니다.WITH원하는 출력을 생성할 수 있는 기능(물론 이 기능을 저장할 수도 있습니다):

with
  function json_keys(j varchar2) return clob as
    jo json_object_t;
    k json_key_list;
    r clob;
  begin
    jo := json_object_t(j);
    k := jo.get_keys();
    select coalesce(
      json_arrayagg(column_value returning clob),
      json_array(returning clob)
    )
    into r
    from table (k);
    return r;
  end;
select o, json_keys(o)
from (
  select '{}' as o from dual union all
  select '{"a":1}' from dual union all
  select '{"a":1,"b":2}' from dual
) t;

결과:

|O            |JSON_KEYS(O)|
|-------------|------------|
|{}           |[]          |
|{"a":1}      |["a"]       |
|{"a":1,"b":2}|["a","b"]   |

시도해 보기:

declare
  jo JSON_OBJECT_T;
  i  NUMBER;
  keys JSON_KEY_LIST;
  CURSOR c_json IS
     SELECT myJsonCol FROM mytable;
begin
  FOR rec IN c_json
  LOOP
     jo := JSON_OBJECT_T.parse(rec.myJsonCol);
     keys := jo.get_keys;
     dbms_output.put_line('KEY VAL');
     FOR i in 1..keys.COUNT
     LOOP
        dbms_output.put_line(keys(i) || ' ' || jo.get_Number(keys(i)));
     END LOOP;
   END LOOP;
END;
/

JSON 값은 단일 튜플이므로 UNPIVOT를 사용하여 키/값 쌍의 테이블로 변환할 수 있습니다.

with mydata as (
  select '{
  "a":9.0847,
  "b":859.947
  }' myjsoncol
  from dual
), q as (
  select json_value(mydata.myjsoncol, '$.a') ca
        ,json_value(mydata.myjsoncol, '$.b') cb
  from mydata
) select * from q
unpivot (val for key in (ca as 'a', cb as 'b'));

KEY VAL
=== =======
a   9.0847
b   859.947

실시간 SQL: https://livesql.oracle.com/apex/livesql/s/d31n9re90y6cpghi4i3m9hfoh

Oracle 11g 버전 Json 조작은 지원되지 않습니다.그래서 기본적인 기능인 SUBSTER / INSTR / SUBSTER을 사용해야 합니다.

다른 스레드에서 솔루션 확인 : Oracle에서 SQL로 JSON 데이터 조작

언급URL : https://stackoverflow.com/questions/40424591/how-to-get-key-value-as-resultset-from-oracle-json-column-using-json-table