Categorias:: Funções de dados semiestruturados e estruturados (Análise)

PARSE_JSON¶

Interpreta uma cadeia de caracteres de entrada como um documento JSON, produzindo um valor VARIANT.

É possível usar a função PARSE_JSON quando tiver dados de entrada no formato JSON. Esta função pode converter dados do formato JSON para ARRAY ou dados OBJECT e armazenar esses dados diretamente em um valor VARIANT. É possível então analisar ou manipular os dados.

Consulte também:: TRY_PARSE_JSON

Sintaxe¶

PARSE_JSON( <expr> )

Copy

Argumentos¶

expr: Uma expressão do tipo cadeia de caracteres (por exemplo, VARCHAR) com informações JSON válidas.

Retornos¶

Retorna um valor do tipo VARIANT com um documento JSON.

Se a entrada for NULL, a função retorna NULL.

Esta função não retorna um tipo estruturado.

Notas de uso¶

Esta função oferece suporte a uma expressão de entrada com um tamanho máximo de 8 MB comprimido.
Se a função PARSE_JSON for chamada com uma cadeia de caracteres vazia, ou com uma cadeia de caracteres contendo apenas caracteres de espaço em branco, então a função retornará NULL (em vez de lançar um erro), mesmo que uma cadeia de caracteres vazia não seja JSON válido. Isto permite que o processamento continue em vez de anular se algumas entradas forem cadeias de caracteres vazias.
Se a entrada for NULL, a saída também será NULL. Entretanto, se a cadeia de caracteres de entrada for 'null', então ela será interpretada como um valor JSON nulo para que o resultado não seja SQL NULL, mas um valor VARIANT válido contendo null. Consulte o exemplo abaixo.
Ao analisar números decimais, PARSE_JSON tenta preservar a exatidão da representação tratando 123,45 como NUMBER(5,2), não como um valor DOUBLE. Entretanto, números que usam notação científica (por exemplo, 1,2345e+02) ou números que não podem ser armazenados como decimais de ponto fixo devido às limitações de intervalo ou escala são armazenados como valores DOUBLE. Como JSON não representa valores como TIMESTAMP, DATE, TIME ou BINARY nativamente, esses valores devem ser representados como cadeias de caracteres.
Em JSON, um objeto (também chamado de “dicionário” ou “hash”) é um conjunto desordenado de pares chave-valor.

TO_JSON e PARSE_JSON são funções (quase) conversas ou recíprocas.
- A função PARSE_JSON usa uma cadeia de caracteres como entrada e retorna um VARIANT compatível com JSON.
- A função TO_JSON usa um VARIANT compatível com JSON e retorna uma cadeia de caracteres.
O seguinte é (conceitualmente) verdadeiro se X for uma cadeia de caracteres contendo JSON válido:

X = TO_JSON(PARSE_JSON(X));

Por exemplo, o seguinte é (conceitualmente) verdadeiro:

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

No entanto, as funções não são perfeitamente recíprocas porque:
- Cadeias de caracteres vazias e cadeias de caracteres com apenas espaço em branco não são tratadas reciprocamente. Por exemplo, o valor de retorno de PARSE_JSON('') é NULL, mas o valor de retorno de TO_JSON(NULL) é NULL, não o '' recíproco.
- A ordem dos pares chave-valor na cadeia de caracteres produzida por TO_JSON não é previsível.
- A cadeia de caracteres produzida por TO_JSON pode ter menos espaço em branco do que a cadeia de caracteres passada para PARSE_JSON.
Por exemplo, os seguintes são JSON equivalentes, mas não cadeias de caracteres equivalentes:
- {"pi": 3.14, "e": 2.71}
- {"e":2.71,"pi":3.14}

Exemplos¶

Os exemplos a seguir usam a função PARSE_JSON.

Armazenamento de valores de diferentes tipos de dados em uma coluna VARIANT¶

Este exemplo armazena diferentes tipos de dados em uma coluna VARIANT chamando PARSE_JSON para analisar cadeias de caracteres.

Criar e preencher uma tabela. A instrução INSERT usa PARSE_JSON para inserir valores VARIANT na coluna v da tabela.

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;

Copy

Consultar os dados. A consulta usa a função TYPEOF para mostrar os tipos de dados dos valores armazenados nos valores VARIANT.

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

Copy

+---+------------------------+------------+
| 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              |            |
|   | }                      |            |
+---+------------------------+------------+

Manipulação de valores NULL com as funções PARSE_JSON e TO_JSON¶

O exemplo a seguir mostra como PARSE_JSON e TO_JSON manipula valores NULL:

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

Copy

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

Comparação de PARSE_JSON e TO_JSON¶

Os exemplos a seguir demonstram a relação entre as funções PARSE_JSON e TO_JSON.

Este exemplo cria uma tabela com uma coluna VARCHAR e uma coluna VARIANT. A instrução INSERT insere um valor VARCHAR, e a instrução UPDATE gera um valor JSON que corresponde a esse valor VARCHAR.

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

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

UPDATE jdemo2 SET variant1 = PARSE_JSON(varchar1);

Copy

Esta consulta mostra que TO_JSON e PARSE_JSON são funções conceitualmente recíprocas:

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

Copy

+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
| 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 |                   |                                 |                              |
|             | }                    | }            |                   |                                 |                              |
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+

Entretanto, as funções não são exatamente recíprocas. Diferenças nos espaços em branco ou na ordem dos pares chave-valor podem impedir que a saída corresponda à entrada. Por exemplo:

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}';

Copy

+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| 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                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+

Comparação de PARSE_JSON e TO_VARIANT¶

Embora tanto a função PARSE_JSON quanto a função TO_VARIANT possam receber uma cadeia de caracteres e retornar um valor VARIANT, elas não são equivalentes. O exemplo a seguir cria uma tabela com duas colunas VARIANT. Em seguida, ele usa PARSE_JSON para inserir um valor em uma coluna e TO_VARIANT para inserir um valor na outra coluna.

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}');

Copy

A consulta abaixo mostra que as funções retornaram valores VARIANT que armazenam valores de diferentes tipos de dados.

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

Copy

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