Unnest JSON Array

개요

PostgreSQL의 UNNEST 함수는 배열 값을 로우 형식으로 반환합니다. 다수의 배열 값을 입력하면 같은 위치의 배열 값이 하나의 로우로 반환됩니다. Oracle Database는 JSON 배열과 PL/SQL 함수로 유사한 기능을 구현할 수 있습니다.

 

PostgreSQL

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

-- 1
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 INTEGER[], c2 TEXT[]);

INSERT INTO t1 VALUES ('{1,2,3}', '{"A","B"}');
-- INSERT INTO t1 VALUES (ARRAY[1,2,3], ARRAY['A','B']);
COMMIT;

 

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

-- 2-1
SELECT UNNEST (c1) AS c1, UNNEST (c2) AS c2 FROM t1;

 c1 | c2
----+----
  1 | A
  2 | B
  3 |

(3 rows)

-- 2-2
SELECT b.c1, b.c2 FROM t1 a, UNNEST (a.c1, a.c2) b (c1, c2);

 c1 | c2
----+----
  1 | A
  2 | B
  3 |

(3 rows)

 

Oracle Database

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

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

INSERT INTO t1 VALUES ('[1,2,3]', '["A","B"]');
COMMIT;

 

JSON_TABLE 표현식은 단일 JSON 배열을 로우 형식으로 반환할 수 있지만 다수의 JSON 배열을 처리할 수 없습니다.

-- 4-1
SELECT b.c1
  FROM t1 a
     , JSON_TABLE (a.c1, '$[*]' COLUMNS c1 NUMBER PATH '$[0]') b;

C1
--
 1
 2
 3

3 행이 선택되었습니다.

-- 4-2
SELECT b.c1, c.c2
  FROM t1 a
     , JSON_TABLE (a.c1, '$[*]' COLUMNS c1 NUMBER         PATH '$[0]') b
     , JSON_TABLE (a.c2, '$[*]' COLUMNS c2 VARCHAR2(4000) PATH '$[0]') c;

C1 C2
-- --
 1 A
 1 B
 2 A
 2 B
 3 A
 3 B

6 행이 선택되었습니다.

 

아래 fnc_json_array_unnest 함수는 입력된 JSON 배열을 UNNEST 가능한 JSON 배열로 변환합니다.

-- 5
CREATE OR REPLACE FUNCTION fnc_json_array_unnest (i_source IN VARCHAR2)
    RETURN VARCHAR2
IS
    v_max_size NUMBER       := 0;
    v_source   JSON_ARRAY_T := JSON_ARRAY_T (i_source);
    v_buffer   JSON_ARRAY_T := JSON_ARRAY_T ();
    v_result   JSON_ARRAY_T := JSON_ARRAY_T ();
BEGIN
    FOR i IN 0 .. v_source.GET_SIZE - 1 LOOP
        v_max_size := GREATEST (v_max_size, JSON_ARRAY_T (v_source.GET_STRING (i)).GET_SIZE);
    END LOOP;

    FOR i IN 0 .. v_max_size - 1 LOOP
        FOR j IN 0 .. v_source.GET_SIZE - 1 LOOP
            v_buffer.APPEND (JSON_ARRAY_T (v_source.GET_STRING (j)).GET_STRING (i));
        END LOOP;
        v_result.APPEND (v_buffer);
        v_buffer := JSON_ARRAY_T ();
    END LOOP;

    RETURN v_result.STRINGIFY;
END;
/

 

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

-- 6
SELECT c1
     , c2
     , json_array (c1, c2) AS json_array
     , fnc_json_array_unnest (json_array (c1, c2)) AS json_array_unnest
  FROM t1;

C1      C2        JSON_ARRAY                  JSON_ARRAY_UNNEST
------- --------- --------------------------- --------------------------------
[1,2,3] ["A","B"] ["[1,2,3]","[\"A\",\"B\"]"] [["1","A"],["2","B"],["3",null]]

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

 

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

-- 7
SELECT b.c1, b.c2
  FROM t1 a
     , JSON_TABLE (fnc_json_array_unnest (JSON_ARRAY (a.c1, a.c2))
                 , '$[*]' COLUMNS (
                       c1 NUMBER         PATH '$[0]'
                     , c2 VARCHAR2(4000) PATH '$[1]')) b;

C1 C2
-- --
 1 A
 2 B
 3

3 행이 선택되었습니다.