카테고리:: 반정형 및 정형 데이터 함수 (구문 분석)

PARSE_JSON¶

입력 문자열을 JSON 문서로 해석하여 VARIANT 값을 생성합니다.

JSON 형식의 입력 데이터가 있는 경우 PARSE_JSON 함수를 사용할 수 있습니다. 이 함수는 JSON 형식의 데이터를 ARRAY 또는 OBJECT 데이터로 변환하고 해당 데이터를 직접 VARIANT 값으로 저장할 수 있습니다. 그런 다음 데이터를 분석하거나 조작할 수 있습니다.

기본적으로 이 함수는 JSON 오브젝트에서 키 중복을 허용하지 않지만 'parameter' 인자를 설정하여 키 중복을 허용할 수 있습니다.

참고 항목:: TRY_PARSE_JSON

구문¶

PARSE_JSON( <expr> [ , '<parameter>' ] )

인자¶

필수:

expr: 유효한 JSON 정보를 보유하는 문자열 형식(예: VARCHAR)의 식입니다.

선택 사항:

'parameter'

일치 항목을 검색하는 데 사용되는 매개 변수를 지정하는 문자열 상수입니다. 지원되는 값:


매개 변수	설명
`d`	JSON 오브젝트에서 중복 키 허용. JSON 오브젝트에 중복 키가 포함된 경우 반환된 오브젝트에는 해당 키에 대해 지정된 마지막 값을 가진 해당 키의 단일 인스턴스가 있습니다.
`s`	JSON 오브젝트에서 중복 키 허용 안 함(엄격). 이 값이 기본값입니다.

반환¶

JSON 문서를 포함하는 VARIANT 타입의 값을 반환합니다.

입력값이 NULL인 경우 이 함수는 NULL을 반환합니다.

이 함수는 정형 유형 을 반환하지 않습니다.

사용법 노트¶

이 함수는 압축된 최대 크기가 64MB인 입력 식을 지원합니다.
빈 문자열 또는 공백 문자만 포함된 문자열로 PARSE_JSON 함수를 호출하면 빈 문자열이 유효하지 않은 JSON 이더라도 오류를 발생시키지 않고 NULL 을 반환합니다. 이 동작을 사용하면 일부 입력이 빈 문자열인 경우 처리를 중단하지 않고 계속할 수 있습니다.
입력이 NULL이면 출력도 NULL입니다. 그러나 입력 문자열이 'null' 인 경우 JSON null 값으로 해석되므로 결과는 SQL NULL 이 아니라 null 을 포함하는 유효한 VARIANT 값이 됩니다. 아래 예를 참조하십시오.
10진수를 구문 분석할 때 PARSE_JSON은 123.45를 DOUBLE 값이 아닌 NUMBER(5,2)로 처리하여 표현의 정확성을 유지하려고 시도합니다. 그러나 과학적 표기법을 사용하는 숫자(예: 1.2345e+02)나, 범위 또는 스케일 제한으로 인해 고정 소수점 10진수로 저장할 수 없는 숫자는 DOUBLE 값으로 저장됩니다. JSON은 기본적으로 TIMESTAMP, DATE, TIME 또는 BINARY와 같은 값을 나타내지 않으므로 이들 값을 문자열로 표현해야 합니다.
JSON에서 오브젝트(“사전” 또는 “해시”라고도 함)는 순서가 지정되지 않은 키-값 페어의 세트입니다.

TO_JSON 및 PARSE_JSON 은 (거의) 역 또는 상호 함수입니다.
- PARSE_JSON 함수는 문자열을 입력값으로 받아 JSON 호환 VARIANT 를 반환합니다.
- TO_JSON 함수는 JSON 호환 VARIANT를 받아 문자열을 반환합니다.
X가 유효한 JSON을 포함하는 문자열인 경우, 다음은 (개념적으로) true입니다.

X = TO_JSON(PARSE_JSON(X));

예를 들어, 다음은 (개념적으로) true입니다.

'{"pi":3.14,"e":2.71}' = TO_JSON(PARSE_JSON('{"pi":3.14,"e":2.71}'))

그러나 다음과 같은 이유로 이 함수는 완벽하게 상호적이지는 않습니다.
- 빈 문자열과 공백만 있는 문자열은 상호적으로 처리되지 않습니다. 예를 들어 PARSE_JSON('') 의 반환 값은 NULL이지만, TO_JSON(NULL) 의 반환 값은 상호적 역 관계의 '' 가 아니라 NULL입니다.
- TO_JSON 에 의해 생성된 문자열의 키-값 페어의 순서는 예측할 수 없습니다.
- TO_JSON 에 의해 생성된 문자열은 PARSE_JSON 에 전달된 문자열보다 공백이 적을 수 있습니다.
예를 들어 다음은 동등한 JSON이지만, 동등한 문자열은 아닙니다.
- {"pi": 3.14, "e": 2.71}
- {"e":2.71,"pi":3.14}

예¶

다음 예제에서는 PARSE_JSON 함수를 사용합니다.

VARIANT 열에 다양한 데이터 타입의 값 저장하기¶

이 예에서는 문자열을 구문 분석하기 위해 PARSE_JSON 을 호출함으로써 VARIANT 열에 다양한 형식의 데이터를 저장합니다.

테이블을 만들고 채웁니다. INSERT 문에서는 PARSE_JSON을 사용하여 테이블의 v 열에 VARIANT 값을 삽입합니다.

CREATE OR REPLACE TABLE vartab (n NUMBER(2), v VARIANT);

INSERT INTO vartab
  SELECT column1 AS n, PARSE_JSON(column2) AS v
    FROM VALUES (1, 'null'), 
                (2, null), 
                (3, 'true'),
                (4, '-17'), 
                (5, '123.12'), 
                (6, '1.912e2'),
                (7, '"Om ara pa ca na dhih"  '), 
                (8, '[-1, 12, 289, 2188, false,]'), 
                (9, '{ "x" : "abc", "y" : false, "z": 10} ') 
       AS vals;

데이터를 쿼리합니다. 이 쿼리는 TYPEOF 함수를 사용하여 VARIANT 값에 저장된 값의 데이터 타입을 표시합니다.

SELECT n, v, TYPEOF(v)
  FROM vartab
  ORDER BY n;

+---+------------------------+------------+
| N | V                      | TYPEOF(V)  |
|---+------------------------+------------|
| 1 | null                   | NULL_VALUE |
| 2 | NULL                   | NULL       |
| 3 | true                   | BOOLEAN    |
| 4 | -17                    | INTEGER    |
| 5 | 123.12                 | DECIMAL    |
| 6 | 1.912000000000000e+02  | DOUBLE     |
| 7 | "Om ara pa ca na dhih" | VARCHAR    |
| 8 | [                      | ARRAY      |
|   |   -1,                  |            |
|   |   12,                  |            |
|   |   289,                 |            |
|   |   2188,                |            |
|   |   false,               |            |
|   |   undefined            |            |
|   | ]                      |            |
| 9 | {                      | OBJECT     |
|   |   "x": "abc",          |            |
|   |   "y": false,          |            |
|   |   "z": 10              |            |
|   | }                      |            |
+---+------------------------+------------+

VARIANT 값에 중복 키가 있는 JSON 오브젝트를 삽입합니다¶

VARIANT 값에 중복 키가 있는 JSON 오브젝트를 삽입해 봅니다.

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2) AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

기본적으로 중복 키는 허용되지 않으므로 오류가 반환됩니다.

100069 (22P02): Error parsing JSON: duplicate object attribute "a", pos 31

VARIANT 값에 중복 키가 있는 JSON 오브젝트를 삽입하고 d 매개 변수를 지정하여 중복을 허용합니다.

INSERT INTO vartab
SELECT column1 AS n, PARSE_JSON(column2, 'd') AS v
  FROM VALUES (10, '{ "a" : "123", "b" : "456", "a": "789"} ')
     AS vals;

+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+

테이블에 대한 쿼리는 마지막 중복 키의 값만 삽입되었음을 보여줍니다.

SELECT v
  FROM vartab
  WHERE n = 10;

+---------------+
| V             |
|---------------|
| {             |
|   "a": "789", |
|   "b": "456"  |
| }             |
+---------------+

PARSE_JSON 및 TO_JSON 함수로 NULL 값 처리하기¶

다음 예에서는 PARSE_JSON 및 TO_JSON이 NULL 값을 처리하는 방법을 보여줍니다.

SELECT TO_JSON(NULL), TO_JSON('null'::VARIANT),
       PARSE_JSON(NULL), PARSE_JSON('null');

+---------------+--------------------------+------------------+--------------------+
| TO_JSON(NULL) | TO_JSON('NULL'::VARIANT) | PARSE_JSON(NULL) | PARSE_JSON('NULL') |
|---------------+--------------------------+------------------+--------------------|
| NULL          | "null"                   | NULL             | null               |
+---------------+--------------------------+------------------+--------------------+

PARSE_JSON과 TO_JSON 비교하기¶

다음 예에서는 PARSE_JSON 함수와 TO_JSON 함수 간의 관계를 보여줍니다.

이 예에서는 VARCHAR 열과 VARIANT 열이 있는 테이블을 만듭니다. INSERT 문은 VARCHAR 값을 삽입하고, UPDATE 문은 그 VARCHAR 값에 상응하는 JSON 값을 생성합니다.

CREATE OR REPLACE TABLE jdemo2 (
  varchar1 VARCHAR, 
  variant1 VARIANT);

INSERT INTO jdemo2 (varchar1) VALUES ('{"PI":3.14}');

UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);

이 쿼리는 TO_JSON 및 PARSE_JSON 이 개념적으로 상호 함수임을 보여줍니다.

SELECT varchar1, 
       PARSE_JSON(varchar1), 
       variant1, 
       TO_JSON(variant1),
       PARSE_JSON(varchar1) = variant1, 
       TO_JSON(variant1) = varchar1
  FROM jdemo2;

+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
| VARCHAR1    | PARSE_JSON(VARCHAR1) | VARIANT1     | TO_JSON(VARIANT1) | PARSE_JSON(VARCHAR1) = VARIANT1 | TO_JSON(VARIANT1) = VARCHAR1 |
|-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------|
| {"PI":3.14} | {                    | {            | {"PI":3.14}       | True                            | True                         |
|             |   "PI": 3.14         |   "PI": 3.14 |                   |                                 |                              |
|             | }                    | }            |                   |                                 |                              |
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+

하지만 이들 함수가 정확히 상호 역 관계는 아닙니다. 공백 또는 키-값 페어의 순서 차이로 인해 출력이 입력과 일치하지 않을 수 있습니다. 예:

SELECT TO_JSON(PARSE_JSON('{"b":1,"a":2}')),
       TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"b":1,"a":2}',
       TO_JSON(PARSE_JSON('{"b":1,"a":2}')) = '{"a":2,"b":1}';

+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| TO_JSON(PARSE_JSON('{"B":1,"A":2}')) | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"B":1,"A":2}' | TO_JSON(PARSE_JSON('{"B":1,"A":2}')) = '{"A":2,"B":1}' |
|--------------------------------------+--------------------------------------------------------+--------------------------------------------------------|
| {"a":2,"b":1}                        | False                                                  | True                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+

PARSE_JSON과 TO_VARIANT 비교하기¶

PARSE_JSON 함수와 TO_VARIANT 함수는 모두 문자열을 받아서 VARIANT 값을 반환할 수 있지만, 두 함수가 동일한 것은 아닙니다. 다음 예에서는 2개의 VARIANT 열이 있는 테이블을 만듭니다. 그런 다음 PARSE_JSON을 사용하여 한 열에 값을 삽입하고 TO_VARIANT를 사용하여 다른 열에 값을 삽입합니다.

CREATE OR REPLACE TABLE jdemo3 (
  variant1 VARIANT,
  variant2 VARIANT);

INSERT INTO jdemo3 (variant1, variant2)
  SELECT
    PARSE_JSON('{"PI":3.14}'),
    TO_VARIANT('{"PI":3.14}');

아래 쿼리는 이들 함수가 서로 다른 데이터 타입의 값을 저장하는 VARIANT 값을 반환했음을 보여줍니다.

SELECT variant1,
       TYPEOF(variant1),
       variant2,
       TYPEOF(variant2),
       variant1 = variant2
  FROM jdemo3;

+--------------+------------------+-----------------+------------------+---------------------+
| VARIANT1     | TYPEOF(VARIANT1) | VARIANT2        | TYPEOF(VARIANT2) | VARIANT1 = VARIANT2 |
|--------------+------------------+-----------------+------------------+---------------------|
| {            | OBJECT           | "{\"PI\":3.14}" | VARCHAR          | False               |
|   "PI": 3.14 |                  |                 |                  |                     |
| }            |                  |                 |                  |                     |
+--------------+------------------+-----------------+------------------+---------------------+