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.

Consulte também:

TRY_PARSE_JSON

Sintaxe

PARSE_JSON( <expr> )
Copy

Argumentos

expr

Uma expressão do tipo cadeia de caracteres (por exemplo, VARCHAR) que contém informações JSON válidas.

Retornos

O valor retornado é do tipo VARIANT e contém um documento JSON.

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. Um exemplo está incluído na seção Exemplos abaixo.

  • Ao analisar números decimais, PARSE_JSON tenta preservar a exatidão da representação, tratando 123,45 como NUMBER(5,2), e não como DOUBLE. Entretanto, os números que utilizam notação científica (por exemplo, 1.2345e+02) ou números que não podem ser armazenados como decimais de pontos fixos devido a limitações de faixa ou escala são armazenados como DOUBLE. Como JSON não representa valores como TIMESTAMP, DATE, TIME ou BINARY nativamente, eles têm que 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) não é ''.

    • 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

Isto mostra um exemplo de armazenamento de diferentes tipos de dados em uma coluna VARIANT chamando PARSE_JSON para analisar cadeias de caracteres.

Criar e preencher uma tabela. Observe que a instrução INSERT usa a função PARSE_JSON.

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:

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

O exemplo a seguir mostra o tratamento de NULL para PARSE_JSON e TO_JSON:

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

Os exemplos a seguir demonstram a relação entre PARSE_JSON, TO_JSON e TO_VARIANT:

Criar uma tabela e adicionar dados VARCHAR, genéricos VARIANT e VARIANT compatíveis com JSON. A instrução INSERT insere um valor VARCHAR, e a instrução UPDATE gera um valor JSON que corresponde a esse VARCHAR.

CREATE or replace TABLE jdemo2 (varchar1 VARCHAR, variant1 VARIANT,
    variant2 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;
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
| 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 |                   |                                 |                              |
|             | }                    | }            |                   |                                 |                              |
+-------------+----------------------+--------------+-------------------+---------------------------------+------------------------------+
Copy

No entanto, as funções não são exatamente recíprocas; diferenças no espaço 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}'
    ;
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
| 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                                                   |
+--------------------------------------+--------------------------------------------------------+--------------------------------------------------------+
Copy

Embora ambos PARSE_JSON e TO_VARIANT possam pegar uma cadeia de caracteres e retornar uma variante, eles não são equivalentes. O seguinte código usa PARSE_JSON para atualizar uma coluna e TO_VARIANT para atualizar a outra coluna. (A atualização da coluna variant1 é desnecessária porque foi atualizada anteriormente usando uma chamada de função idêntica; entretanto, o código abaixo a atualiza novamente para que você possa ver lado a lado quais funções são chamadas para atualizar as colunas).

UPDATE jdemo2 SET
    variant1 = PARSE_JSON(varchar1),
    variant2 = TO_VARIANT(varchar1);
Copy

A consulta abaixo mostra que a saída de PARSE_JSON e a saída de TO_VARIANT não são a mesma. Além da(s) diferença(s) trivial(is) no espaço em branco, existem diferenças significativas nas aspas.

SELECT variant1, variant2, variant1 = variant2 FROM jdemo2;
+--------------+-----------------+---------------------+
| VARIANT1     | VARIANT2        | VARIANT1 = VARIANT2 |
|--------------+-----------------+---------------------|
| {            | "{\"PI\":3.14}" | False               |
|   "PI": 3.14 |                 |                     |
| }            |                 |                     |
+--------------+-----------------+---------------------+
Copy