JSON_EACH 함수

개요

PostgreSQL의 JSON_EACH 함수는 key-value로 구성된 JSON 오브젝트를 로우 형식으로 반환합니다. Oracle Database는 JSON_EACH 함수를 제공하지 않아 PL/SQL 함수를 통해 JSON_EACH 함수 기능을 구현해야 합니다.

 

PostgreSQL

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 JSON);
INSERT INTO t1 VALUES ('{"A":1,"B":2}');
COMMIT;

 

JSON_EACH 함수는 아래와 같이 동작합니다.

-- 2
SELECT b.key, b.value FROM t1 a, JSON_EACH (a.c1) b;

 key | value
-----+-------
 A   |     1
 B   |     2

(2 rows)

 

Oracle Database

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 3
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 VARCHAR2(4000));

INSERT INTO t1 VALUES ('{"A":1,"B":2}');
COMMIT;

 

아래 fnc_json_obj_to_arr 함수는 JSON 오브젝트를 JSON 배열로 변환합니다.

-- 4
CREATE OR REPLACE FUNCTION fnc_json_obj_to_arr (i_value IN VARCHAR2)
    RETURN VARCHAR2
IS
    v_json_object JSON_OBJECT_T := JSON_OBJECT_T (i_value);
    v_json_key    JSON_KEY_LIST := v_json_object.GET_KEYS;
    v_buffer      JSON_ARRAY_T  := JSON_ARRAY_T ();
    v_result      JSON_ARRAY_T  := JSON_ARRAY_T ();
BEGIN
    FOR i in 1 .. v_json_key.COUNT LOOP
        v_buffer := JSON_ARRAY_T ();
        v_buffer.APPEND (v_json_key(i));
        v_buffer.APPEND (v_json_object.GET_STRING(v_json_key(i)));
        v_result.APPEND (v_buffer);
    END LOOP;

    RETURN v_result.STRINGIFY;
END;
/

 

아래는 fnc_json_obj_to_arr 함수를 사용한 결과입니다.

-- 5
SELECT c1, fnc_json_obj_to_arr (c1) AS json_obj_to_arr FROM t1;

C1            JSON_OBJ_TO_ARR
------------- ---------------------
{"A":1,"B":2} [["A","1"],["B","2"]]

1개의 행이 선택되었습니다.

 

fnc_json_obj_to_arr 함수와 JSON_TABLE 표현식을 사용하면 PostgreSQL의 JSON_EACH 함수와 동일한 결과를 얻을 수 있습니다.

-- 6
SELECT b.key, b.value
  FROM t1 a
     , JSON_TABLE (fnc_json_obj_to_arr (a.c1)
                 , '$[*]' COLUMNS (
                       key   VARCHAR2(4000) PATH '$[0]'
                     , value NUMBER         PATH '$[1]')) b;

KEY VALUE
--- -----
A       1
B       2

2 행이 선택되었습니다.