Funções JSON no SQL padrão

O BigQuery é compatível com funções que ajudam você a recuperar dados armazenados nas strings formatadas como JSON e funções que ajudam a transformar dados em strings formatadas como JSON.

JSON_EXTRACT ou JSON_EXTRACT_SCALAR

JSON_EXTRACT(json_string_expr, json_path_string_literal), que retorna valores JSON como STRINGs.

JSON_EXTRACT_SCALAR(json_string_expr, json_path_string_literal), que retorna valores JSON escalares como STRINGs.

Descrição

O parâmetro json_string_expr precisa ser uma string formatada como JSON. Exemplo:

{"class" : {"students" : [{"name" : "Jane"}]}}

O parâmetro json_path_string_literal identifica o valor ou os valores que você deseja receber da string formatada como JSON. Crie esse parâmetro usando o formato JSONPath. O parâmetro que é parte desse formato precisa iniciar com o símbolo $, que indica o nível mais externo da string formatada como JSON. Identifique os valores filho usando uma notação de ponto ou colchete. Se o objeto JSON for uma matriz, use colchetes para especificar o índice da matriz.

JSONPath Descrição
$ Objeto ou elemento raiz
. ou [] Operador filho
[] Operador subscrito

Ambas as funções retornam NULL, se o parâmetro json_path_string_literal não corresponder a um valor de json_string_expr. Se o valor selecionado de JSON_EXTRACT_SCALAR não for escalar, como um objeto ou matriz, a função retornará NULL.

Se o JSONPath for inválido, essas funções gerarão um erro.

Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas simples e colchetes para inserir caracteres de escape, [' ']. Por exemplo:

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;

+-------+
| hello |
+-------+
| world |
+-------+

Exemplos

SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

A consulta acima apresenta o seguinte resultado:

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

A consulta acima apresenta o seguinte resultado:

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

A consulta acima apresenta o seguinte resultado:

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"Jamie"} |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

A consulta acima apresenta o seguinte resultado:

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

A consulta acima apresenta o seguinte resultado:

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Descrição

Retorna uma representação de string formatada em JSON de value. Essa função é compatível com o parâmetro pretty_print opcional. Se pretty_print estiver presente, o valor retornado será formatado para facilitar a leitura.

Tipo de dados de entrada Valor retornado
NULL de qualquer tipo null
BOOL true ou false.
INT64

Igual a CAST(value AS STRING) quando value está no intervalo de [-253, 253], que é o intervalo de números inteiros que pode ser representado sem perdas como números de ponto flutuante de precisão dupla IEEE 754. Valores fora desse intervalo são representados como strings entre aspas. Por exemplo:

-1
0
12345678901
9007199254740992
-9007199254740992
"9007199254740993"

9007199254740993 é maior que 253. Dessa forma, ele é representado como uma string entre aspas.

NUMERIC

Igual a CAST(value AS STRING) quando value está no intervalo de [-253, 253] e não tem parte fracionária. Valores fora desse intervalo são representados como strings entre aspas. Por exemplo:

-1
0
"9007199254740993"
"123.56"
FLOAT64 +/-inf e NaN são representados como Infinity, -Infinity e NaN, respectivamente.

Caso contrário, é o mesmo que CAST(value AS STRING).

STRING Valor de string entre aspas, com escape de acordo com o padrão JSON. Especificamente, ", \ e os caracteres de controle de U+0000 a U+001F são escapados.
BYTES

Valor de escape de base64 RFC 4648 entre aspas. Por exemplo:

"R29vZ2xl" é a representação base64 de bytes b"Google"

DATE

Data entre aspas. Por exemplo:

"2017-03-06"
TIMESTAMP

Data/hora ISO 8601 entre aspas, em que T separa a data e a hora e Zulu/UTC representa o fuso horário. Por exemplo:

"2017-03-06T12:34:56.789012Z"
DATETIME

Data/hora ISO 8601 entre aspas, em que T separa a data e a hora. Por exemplo:

"2017-03-06T12:34:56.789012"
TIME

Hora ISO 8601 entre aspas. Por exemplo:

"12:34:56.789012"
ARRAY

[elem1,elem2,...], em que cada elem é formatado de acordo com o tipo de elemento.

Exemplo com formatação:

[
  elem1,
  elem2,
  ...
]

Onde cada elem é formatado de acordo com o tipo de elemento. A matriz vazia é representada como [].

STRUCT {"field_name1":field_value1,"field_name2":field_value2,...}

Onde cada field_value é formatado de acordo com seu tipo.

Exemplo com formatação:

{
  "field_name1": field_value1,
  "field_name2": field_value2,
  ...
}

Onde cada field_value é formatado de acordo com seu tipo. Se um field_value for um ARRAY ou STRUCT não vazio, os elementos serão recuados no nível apropriado. A estrutura vazia é representada como {}.

Os campos com nomes duplicados podem resultar em JSON não analisável. Os campos anônimos são representados com "".

Os nomes de campos UTF-8 inválidos podem resultar em JSON não analisável. Os valores de string são escapados de acordo com o padrão JSON. Especificamente, ", \ e os caracteres de controle de U+0000 a U+001F são escapados.

Tipo de retorno

Representação do valor de string JSON.

Exemplos

Converta linhas em uma tabela para JSON.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  t,
  TO_JSON_STRING(t) AS json_row
FROM Input AS t;

A consulta acima apresenta o seguinte resultado:

+-----------------------------------+-------------------------------------------------------+
| t                                 | json_row                                              |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}}     | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}}    |
| {[3], bar, {NULL, 2016-12-05}}    | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}}   |
+-----------------------------------+-------------------------------------------------------+

Converta linhas em uma tabela para JSON com formatação.

WITH Input AS (
  SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
  SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
  SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
  TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;

A consulta acima apresenta o seguinte resultado:

+-----------------------+
| json_row              |
+-----------------------+
| {                     |
|  "x": [               |
|    1,                 |
|    2                  |
|  ],                   |
|  "y": "foo",          |
|  "s": {               |
|    "a": true,         |
|    "b": "2017-04-05"  |
|  }                    |
|}                      |
| {                     |
|  "x": null,           |
|  "y": "",             |
|  "s": {               |
|    "a": false,        |
|    "b": "0001-01-01"  |
|  }                    |
|}                      |
| {                     |
|  "x": [               |
|    3                  |
|  ],                   |
|  "y": "bar",          |
|  "s": {               |
|    "a": null,         |
|    "b": "2016-12-05"  |
|  }                    |
|}                      |
+-----------------------+
Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.