Funções JSON

Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

O BigQuery é compatível com as funções a seguir, que podem recuperar e transformar dados JSON.

Visão geral da função

As funções a seguir usam aspas duplas para evitar o uso de caracteres de escape JSONPath inválidos: "a.b".

Esse comportamento está de acordo com o padrão ANSI.

Função JSON Descrição Tipo de retorno
JSON_QUERY Extrai um valor JSON, como uma matriz ou objeto, ou um valor escalar em JSON, como uma string, número ou valor booleano. STRING ou JSON formatado em JSON
JSON_VALUE Extrai um valor escalar. Um valor escalar pode representar uma string, um número ou um booleano. Remove as aspas externas e desfaz o escape dos valores. Retorna um NULL SQL quando um valor não escalar é selecionado. STRING
JSON_QUERY_ARRAY Extrai uma matriz de valores JSON, como matrizes ou objetos, e valores escalares em JSON, como strings, números e booleanos. ARRAY<JSON-formatted STRING> ou ARRAY<JSON>
JSON_VALUE_ARRAY Extrai uma matriz de valores escalares. Um valor escalar pode representar uma string, um número ou um booleano. Remove as aspas externas e desfaz o escape dos valores. Retornará um SQL NULL se o valor selecionado não for uma matriz ou não for uma matriz contendo apenas valores escalares. ARRAY<STRING>

Funções JSON legadas de extração

As funções a seguir usam aspas simples e colchetes para evitar o uso de caracteres de escape JSONPath inválidos: ['a.b'].

Embora essas funções sejam compatíveis com o BigQuery, recomendamos usá-las na tabela anterior.

Função JSON Descrição Tipo de retorno
JSON_EXTRACT Extrai um valor JSON, como uma matriz ou objeto, ou um valor escalar em JSON, como uma string, número ou valor booleano. STRING ou JSON formatado em JSON
JSON_EXTRACT_SCALAR Extrai um valor escalar. Um valor escalar pode representar uma string, um número ou um booleano. Remove as aspas externas e desfaz o escape dos valores. Retorna um NULL SQL quando um valor não escalar é selecionado. STRING
JSON_EXTRACT_ARRAY Extrai uma matriz de valores JSON, como matrizes ou objetos, e valores escalares em JSON, como strings, números e booleanos. ARRAY<JSON-formatted STRING> ou ARRAY<JSON>
JSON_EXTRACT_STRING_ARRAY Extrai uma matriz de valores escalares. Um valor escalar pode representar uma string, um número ou um booleano. Remove as aspas externas e desfaz o escape dos valores. Retornará um SQL NULL se o valor selecionado não for uma matriz ou não for uma matriz contendo apenas valores escalares. ARRAY<STRING>

Outras funções JSON

Função JSON Descrição Tipo de retorno
PARSE_JSON Usa uma string formatada em JSON e retorna um valor JSON. JSON
TO_JSON Usa um valor SQL e retorna um valor JSON. JSON
TO_JSON_STRING Usa um valor SQL e retorna uma representação de string formatada em JSON do valor. STRING com formatação JSON
STRING Extrai uma string JSON. STRING
BOOL Extrai um booleano JSON. BOOL
INT64 Extrai um número inteiro de 64 bits JSON. INT64
FLOAT64 Extrai um número de ponto flutuante de 64 bits JSON. FLOAT64
JSON_TYPE Retorna o tipo do valor JSON mais externo como uma string. STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)
JSON_EXTRACT(json_expr, json_path)

Descrição

Extrai um valor JSON, como uma matriz ou objeto, ou um valor escalar em JSON, como uma string, número ou valor booleano. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas simples e colchetes.

  • json_string_expr: uma string formatada em JSON. Exemplo:

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

    Extrai um NULL SQL quando uma string formatada em JSON null é encontrada. Exemplo:

    SELECT JSON_EXTRACT("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON. Exemplo:

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

    Extrai um null JSON quando um null JSON é encontrado.

    SELECT JSON_EXTRACT(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada.

Tipo de retorno

  • json_string_expr: um STRING formatado em JSON
  • json_expr: JSON

Exemplos

No exemplo a seguir, os dados JSON são extraídos e retornados como JSON.

SELECT
  JSON_EXTRACT(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

Nos exemplos a seguir, os dados JSON são extraídos e retornados como strings formatadas em JSON.

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;

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

+-----------------+
| 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" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "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;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_EXTRACT('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_EXTRACT(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)
JSON_QUERY(json_expr, json_path)

Descrição

Extrai um valor JSON, como uma matriz ou objeto, ou um valor escalar em JSON, como uma string, número ou valor booleano. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas duplas.

  • json_string_expr: uma string formatada em JSON. Exemplo:

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

    Extrai um NULL SQL quando uma string formatada em JSON null é encontrada. Exemplo:

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
    
  • json_expr: JSON. Exemplo:

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

    Extrai um null JSON quando um null JSON é encontrado.

    SELECT JSON_QUERY(JSON 'null', "$") -- Returns a JSON 'null'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada.

Tipo de retorno

  • json_string_expr: um STRING formatado em JSON
  • json_expr: JSON

Exemplos

No exemplo a seguir, os dados JSON são extraídos e retornados como JSON.

SELECT
  JSON_QUERY(JSON '{"class":{"students":[{"id":5},{"id":12}]}}', '$.class')
  AS json_data;

+-----------------------------------+
| json_data                         |
+-----------------------------------+
| {"students":[{"id":5},{"id":12}]} |
+-----------------------------------+

Nos exemplos a seguir, os dados JSON são extraídos e retornados como strings formatadas em JSON.

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

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(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;

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

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

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
SELECT JSON_QUERY(JSON '{"a":null}', "$.a"); -- Returns a JSON 'null'
SELECT JSON_QUERY(JSON '{"a":null}', "$.b"); -- Returns a SQL NULL

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])
JSON_EXTRACT_SCALAR(json_expr[, json_path])

Descrição

Extrai um valor escalar e o retorna como uma string. Um valor escalar pode representar uma string, um número ou um booleano. Remove as aspas mais externas e desfaz o escape para os valores de retorno. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas simples e colchetes.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Exemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que todos os dados serão analisados.

    Se json_path retornar um null JSON ou um valor não escalar (em outras palavras, se json_path referir-se a um objeto ou uma matriz), um NULL SQL será retornado.

Tipo de retorno

STRING

Exemplos

No exemplo a seguir, age é extraído.

SELECT JSON_EXTRACT_SCALAR(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

O exemplo a seguir compara como os resultados são retornados para as funções JSON_EXTRACT e JSON_EXTRACT_SCALAR.

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_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+
SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL                |
+--------------------+---------------------+

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

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

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

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])
JSON_VALUE(json_expr[, json_path])

Descrição

Extrai um valor escalar e o retorna como uma string. Um valor escalar pode representar uma string, um número ou um booleano. Remove as aspas mais externas e desfaz o escape para os valores de retorno. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas duplas.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Exemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que todos os dados serão analisados.

    Se json_path retornar um null JSON ou um valor não escalar (em outras palavras, se json_path referir-se a um objeto ou uma matriz), um NULL SQL será retornado.

Tipo de retorno

STRING

Exemplos

No exemplo a seguir, os dados JSON são extraídos e retornados como um valor escalar.

SELECT JSON_VALUE(JSON '{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+------------+
| scalar_age |
+------------+
| 6          |
+------------+

O exemplo a seguir compara como os resultados são retornados para as funções JSON_QUERY e JSON_VALUE.

SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

+-----------+-------------+----------+------------+
| json_name | scalar_name | json_age | scalar_age |
+-----------+-------------+----------+------------+
| "Jakob"   | Jakob       | "6"      | 6          |
+-----------+-------------+----------+------------+
SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas duplas para inserir caracteres de escape. Exemplo:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') AS hello;

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])
JSON_EXTRACT_ARRAY(json_expr[, json_path])

Descrição

Extrai uma matriz de valores JSON, como matrizes ou objetos, e valores escalares em JSON, como strings, números e booleanos. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas simples e colchetes.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Exemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que todos os dados serão analisados.

Tipo de retorno

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

Exemplos

Isso extrai itens em JSON para uma matriz de valores JSON:

SELECT JSON_EXTRACT_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Isso extrai os itens em uma string formatada em JSON para uma matriz de string:

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') AS string_array;

+--------------+
| string_array |
+--------------+
| [1, 2, 3]    |
+--------------+

Isso extrai uma matriz de strings e a converte em uma matriz de inteiros:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Isso extrai os valores de string de uma string formatada em JSON para uma matriz:

-- Doesn't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Isso extrai apenas os itens da propriedade fruit para uma matriz:

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

Eles são equivalentes:

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

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

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

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

Os exemplos a seguir exploram como as solicitações inválidas e as matrizes vazias são tratadas:

  • Se um JSONPath for inválido, um erro será gerado.
  • Se uma string formatada em JSON for inválida, a saída será NULL.
  • Não há problema em haver matrizes vazias na string formatada em JSON.
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])
JSON_QUERY_ARRAY(json_expr[, json_path])

Descrição

Extrai uma matriz de valores JSON, como matrizes ou objetos, e valores escalares em JSON, como strings, números e booleanos. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas duplas.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Exemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que todos os dados serão analisados.

Tipo de retorno

  • json_string_expr: ARRAY<JSON-formatted STRING>
  • json_expr: ARRAY<JSON>

Exemplos

Isso extrai itens em JSON para uma matriz de valores JSON:

SELECT JSON_QUERY_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS json_array;

+---------------------------------+
| json_array                      |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Isso extrai os itens em uma string formatada em JSON para uma matriz de string:

SELECT JSON_QUERY_ARRAY('[1,2,3]') AS string_array;

+--------------+
| string_array |
+--------------+
| [1, 2, 3]    |
+--------------+

Isso extrai uma matriz de strings e a converte em uma matriz de inteiros:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_QUERY_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Isso extrai os valores de string de uma string formatada em JSON para uma matriz:

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_VALUE(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Isso extrai apenas os itens da propriedade fruit para uma matriz:

SELECT JSON_QUERY_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

Eles são equivalentes:

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas duplas para inserir caracteres de escape: " " Exemplo:

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

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

Os exemplos a seguir mostram como as solicitações inválidas e as matrizes vazias são tratadas:

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])
JSON_EXTRACT_STRING_ARRAY(json_expr[, json_path])

Descrição

Extrai uma matriz de valores escalares e retorna uma matriz de valores escalares formatados em strings. Um valor escalar pode representar uma string, um número ou um booleano. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas simples e colchetes.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Exemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que todos os dados serão analisados.

Advertências:

  • Um JSON null na matriz de entrada produz um SQL NULL como a saída desse JSON null. Se a saída contiver um elemento de matriz NULL, será gerado um erro, porque a saída final não pode ser uma matriz com valores NULL.
  • Se um JSONPath corresponder a uma matriz que contém objetos escalares e um null JSON, a saída da função precisará ser transformada porque a saída final não pode ser uma matriz com valores NULL.

Tipo de retorno

ARRAY<STRING>

Exemplos

Isso extrai itens em JSON para uma matriz de strings:

SELECT JSON_EXTRACT_STRING_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

O exemplo a seguir compara como os resultados são retornados para as funções JSON_EXTRACT_ARRAY e JSON_EXTRACT_STRING_ARRAY.

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

Isso extrai os itens em uma string formatada em JSON para uma matriz de string:

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

Isso extrai uma matriz de strings e a converte em uma matriz de inteiros:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_STRING_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Eles são equivalentes:

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

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

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

+---------+
| hello   |
+---------+
| [world] |
+---------+

Os exemplos a seguir exploram como as solicitações inválidas e as matrizes vazias são tratadas:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_EXTRACT_STRING_ARRAY('["world", 1, null]') AS result;

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])
JSON_VALUE_ARRAY(json_expr[, json_path])

Descrição

Extrai uma matriz de valores escalares e retorna uma matriz de valores escalares formatados em strings. Um valor escalar pode representar uma string, um número ou um booleano. Se uma chave JSON usa caracteres JSONPath inválidos, é possível inserir caracteres de escape usando aspas duplas.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_expr: JSON. Exemplo:

    JSON '{"class" : {"students" : [{"name" : "Jane"}]}}'
    
  • json_path: o JSONpath. Isso identifica os dados que você quer receber da entrada. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que todos os dados serão analisados.

Advertências:

  • Um JSON null na matriz de entrada produz um SQL NULL como a saída para o JSON null. Se a saída contiver um elemento de matriz NULL, será gerado um erro, porque a saída final não pode ser uma matriz com valores NULL.
  • Se um JSONPath corresponder a uma matriz que contém objetos escalares e um null JSON, a saída da função precisará ser transformada porque a saída final não pode ser uma matriz com valores NULL.

Tipo de retorno

ARRAY<STRING>

Exemplos

Isso extrai itens em JSON para uma matriz de strings:

SELECT JSON_VALUE_ARRAY(
  JSON '{"fruits":["apples","oranges","grapes"]}','$.fruits'
  ) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

O exemplo a seguir compara como os resultados são retornados para as funções JSON_QUERY_ARRAY e JSON_VALUE_ARRAY.

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

Isso extrai os itens em uma string formatada em JSON para uma matriz de string:

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

Isso extrai uma matriz de strings e a converte em uma matriz de inteiros:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_VALUE_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

+---------------+
| integer_array |
+---------------+
| [1, 2, 3]     |
+---------------+

Eles são equivalentes:

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

Quando a chave JSON usar caracteres inválidos do JSONPath, use aspas duplas para inserir caracteres de escape: " " Exemplo:

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

+---------+
| hello   |
+---------+
| [world] |
+---------+

Os exemplos a seguir exploram como as solicitações inválidas e as matrizes vazias são tratadas:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- The following query produces and error because the final output cannot be an
-- array with NULLs.
SELECT JSON_VALUE_ARRAY('["world", 1, null]') AS result;

PARSE_JSON

PARSE_JSON(json_string_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Descrição

Usa um valor STRING do SQL e retorna um valor JSON do SQL. O valor STRING representa um valor JSON em formato de string.

Essa função é compatível com um argumento opcional de nome obrigatório chamado wide_number_mode, que determina como processar números que não podem ser armazenados em um valor JSON sem a perda da precisão. Se usado, wide_number_mode precisará incluir um destes valores:

  • exact: aceita apenas números que podem ser armazenados sem perda de precisão. Se for encontrado um número que não pode ser armazenado sem perda de precisão, a função gerará um erro.
  • round: se um número que não pode ser armazenado sem perda de precisão for encontrado, tente arredondar para um número que possa ser armazenado sem perda de precisão. Se o número não puder ser arredondado, a função emitirá um erro.

Se wide_number_mode não for usado, a função incluirá implicitamente wide_number_mode=>'exact'. Se um número aparecer em um objeto ou uma matriz JSON, o argumento wide_number_mode será aplicado ao número no objeto ou na matriz.

Os números dos domínios a seguir podem ser armazenados em JSON sem perda de precisão:

  • Números inteiros assinados/não assinados de 64 bits, como INT64
  • FLOAT64

Tipo de retorno

JSON

Exemplos

No exemplo a seguir, uma string formatada em JSON é convertida para JSON.

SELECT PARSE_JSON('{"coordinates":[10,20],"id":1}') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
+--------------------------------+

As seguintes consultas falham porque:

  • O número passado não pode ser armazenado sem perda de precisão.
  • wide_number_mode=>'exact' é usado implicitamente na primeira consulta e explicitamente na segunda.
SELECT PARSE_JSON('{"id":922337203685477580701}') AS json_data; -- fails
SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'exact') AS json_data; -- fails

A consulta a seguir arredonda o número para um número que pode ser armazenado em JSON.

SELECT PARSE_JSON('{"id":922337203685477580701}', wide_number_mode=>'round') AS json_data;

+--------------------------------+
| json_data                      |
+--------------------------------+
| {"id":9.223372036854776e+20}   |
+--------------------------------+

TO_JSON

TO_JSON(sql_value[, stringify_wide_numbers=>{ TRUE | FALSE }])

Descrição

Usa um valor SQL e retorna um valor JSON. O valor precisa ser um tipo de dados compatível com o BigQuery. Revise os tipos de dados do BigQuery para os quais essa função oferece suporte e as codificações JSON aqui.

Essa função é compatível com um argumento opcional de nome obrigatório chamado stringify_wide_numbers.

  • Se esse argumento for TRUE, os valores numéricos fora do domínio do tipo FLOAT64 serão codificados como strings.
  • Se esse argumento não for usado ou for FALSE, os valores numéricos fora do domínio do tipo FLOAT64 não serão codificados como strings, mas serão armazenados como números JSON. Se um valor numérico não puder ser armazenado em JSON sem perda de precisão, um erro será gerado.

Os seguintes tipos de dados numéricos são afetados pelo argumento stringify_wide_numbers:

  • INT64
  • NUMERIC
  • BIGNUMERIC

Se um desses tipos de dados numéricos aparecer em um tipo de dados de contêiner, como ARRAY ou STRUCT, o argumento stringify_wide_numbers será aplicado aos tipos de dados numéricos no tipo de dados do contêiner. de dados.

Tipo de retorno

Um valor JSON

Exemplos

No exemplo a seguir, a consulta converte linhas em uma tabela em valores JSON.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT TO_JSON(t) AS json_objects
FROM CoordinatesTable AS t;

+--------------------------------+
| json_objects                   |
+--------------------------------+
| {"coordinates":[10,20],"id":1} |
| {"coordinates":[30,40],"id":2} |
| {"coordinates":[50,60],"id":3} |
+--------------------------------+

No exemplo a seguir, a consulta retorna um grande valor numérico como uma string JSON.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>TRUE) as stringify_on

+--------------------+
| stringify_on       |
+--------------------+
| "9007199254740993" |
+--------------------+

No exemplo a seguir, as duas consultas retornam um valor numérico grande como número JSON.

SELECT TO_JSON(9007199254740993, stringify_wide_numbers=>FALSE) as stringify_off
SELECT TO_JSON(9007199254740993) as stringify_off

+------------------+
| stringify_off    |
+------------------+
| 9007199254740993 |
+------------------+

No exemplo a seguir, apenas valores numéricos grandes são convertidos em strings JSON.

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+---------------------------+
| json_objects              |
+---------------------------+
| {"id":"9007199254740993"} |
| {"id":2}                  |
+---------------------------+

Neste exemplo, os valores 9007199254740993 (INT64) e 2.1 (FLOAT64) são convertidos no supertipo em comum FLOAT64, que não é afetado pelo argumento stringify_wide_numbers;

With T1 AS (
  (SELECT 9007199254740993 AS id) UNION ALL
  (SELECT 2.1 AS id))
SELECT TO_JSON(t, stringify_wide_numbers=>TRUE) AS json_objects
FROM T1 AS t;

+------------------------------+
| json_objects                 |
+------------------------------+
| {"id":9.007199254740992e+15} |
| {"id":2.1}                   |
+------------------------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

Descrição

Recebe um valor SQL e retorna uma representação de string formatada em JSON do valor. O valor precisa ser um tipo de dados compatível com o BigQuery. Revise os tipos de dados do BigQuery compatíveis com essa função e as codificações JSON aqui.

Essa função aceita um parâmetro booleano opcional chamado pretty_print. Se pretty_print for true, o valor retornado será formatado para facilitar a leitura.

Tipo de retorno

Um STRING formatado em JSON

Exemplos

Converter linhas em uma tabela para strings formatadas como JSON.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable AS t;

+----+-------------+--------------------------------+
| id | coordinates | json_data                      |
+----+-------------+--------------------------------+
| 1  | [10, 20]    | {"id":1,"coordinates":[10,20]} |
| 2  | [30, 40]    | {"id":2,"coordinates":[30,40]} |
| 3  | [50, 60]    | {"id":3,"coordinates":[50,60]} |
+----+-------------+--------------------------------+

Converter linhas em uma tabela para strings formatadas como JSON fáceis de ler.

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable AS t;

+----+-------------+--------------------+
| id | coordinates | json_data          |
+----+-------------+--------------------+
| 1  | [10, 20]    | {                  |
|    |             |   "id": 1,         |
|    |             |   "coordinates": [ |
|    |             |     10,            |
|    |             |     20             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+
| 2  | [30, 40]    | {                  |
|    |             |   "id": 2,         |
|    |             |   "coordinates": [ |
|    |             |     30,            |
|    |             |     40             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

STRING

STRING(json_expr)

Descrição

Usa uma expressão JSON, extrai uma string JSON e retorna esse valor como um SQL STRING. Se a expressão for SQL NULL, a função retornará SQL NULL. Se o valor JSON extraído não for uma string, ocorrerá um erro.

  • json_expr: JSON. Exemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de retorno

STRING

Exemplos

SELECT STRING(JSON '"purple"') AS color;

+--------+
| color  |
+--------+
| purple |
+--------+
SELECT STRING(JSON_QUERY(JSON '{"name": "sky", "color": "blue"}', "$.color")) AS color;

+-------+
| color |
+-------+
| blue  |
+-------+

Os exemplos a seguir mostram como as solicitações inválidas são tratadas:

-- An error is thrown if the JSON is not of type string.
SELECT STRING(JSON '123') AS result; -- Throws an error
SELECT STRING(JSON 'null') AS result; -- Throws an error
SELECT SAFE.STRING(JSON '123') AS result; -- Returns a SQL NULL

BOOL

BOOL(json_expr)

Descrição

Usa uma expressão JSON, extrai um booleano JSON e retorna esse valor como um SQL BOOL. Se a expressão for SQL NULL, a função retornará SQL NULL. Se o valor JSON extraído não for booleano, ocorrerá um erro.

  • json_expr: JSON. Exemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de retorno

BOOL

Exemplos

SELECT BOOL(JSON 'true') AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+
SELECT BOOL(JSON_QUERY(JSON '{"hotel class": "5-star", "vacancy": true}', "$.vacancy")) AS vacancy;

+---------+
| vacancy |
+---------+
| true    |
+---------+

Os exemplos a seguir mostram como as solicitações inválidas são tratadas:

-- An error is thrown if JSON is not of type bool.
SELECT BOOL(JSON '123') AS result; -- Throws an error
SELECT BOOL(JSON 'null') AS result; -- Throw an error
SELECT SAFE.BOOL(JSON '123') AS result; -- Returns a SQL NULL

INT64

INT64(json_expr)

Descrição

Usa uma expressão JSON, extrai um número JSON e retorna esse valor como um SQL INT64. Se a expressão for SQL NULL, a função retornará SQL NULL. Se o número JSON extraído tiver uma parte fracionária ou estiver fora do domínio INT64, ocorrerá um erro.

  • json_expr: JSON. Exemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de retorno

INT64

Exemplos

SELECT INT64(JSON '2005') AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON_QUERY(JSON '{"gate": "A4", "flight_number": 2005}', "$.flight_number")) AS flight_number;

+---------------+
| flight_number |
+---------------+
| 2005          |
+---------------+
SELECT INT64(JSON '10.0') AS score;

+-------+
| score |
+-------+
| 10    |
+-------+

Os exemplos a seguir mostram como as solicitações inválidas são tratadas:

-- An error is thrown if JSON is not a number or cannot be converted to a 64-bit integer.
SELECT INT64(JSON '10.1') AS result;  -- Throws an error
SELECT INT64(JSON '"strawberry"') AS result; -- Throws an error
SELECT INT64(JSON 'null') AS result; -- Throws an error
SELECT SAFE.INT64(JSON '"strawberry"') AS result;  -- Returns a SQL NULL

FLOAT64

FLOAT64(json_expr[, wide_number_mode=>{ 'exact' | 'round' }])

Descrição

Usa uma expressão JSON, extrai um número JSON e retorna esse valor como um SQL FLOAT64. Se a expressão for SQL NULL, a função retornará SQL NULL. Se o valor JSON extraído não for um número, ocorrerá um erro.

  • json_expr: JSON. Exemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Essa função aceita um argumento opcional de nome obrigatório chamado wide_number_mode, que define o que acontece com um número que não pode ser representado como um FLOAT64 sem perda de precisão.

Esse argumento aceita um dos dois valores, diferenciando maiúsculas de minúsculas:

  • 'exact': a função falha se o resultado não puder ser representado como um FLOAT64 sem perda de precisão.
  • 'round': o valor numérico armazenado em JSON será arredondado para FLOAT64. Se o arredondamento não for possível, a função vai falhar. Esse vai ser o valor padrão se o argumento não for especificado.

Tipo de retorno

FLOAT64

Exemplos

SELECT FLOAT64(JSON '9.8') AS velocity;

+----------+
| velocity |
+----------+
| 9.8      |
+----------+
SELECT FLOAT64(JSON_QUERY(JSON '{"vo2_max": 39.1, "age": 18}', "$.vo2_max")) AS vo2_max;

+---------+
| vo2_max |
+---------+
| 39.1    |
+---------+
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'round') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+
SELECT FLOAT64(JSON '18446744073709551615') as result;

+------------------------+
| result                 |
+------------------------+
| 1.8446744073709552e+19 |
+------------------------+

Os exemplos a seguir mostram como as solicitações inválidas são tratadas:

-- An error is thrown if JSON is not of type FLOAT64.
SELECT FLOAT64(JSON '"strawberry"') AS result;
SELECT FLOAT64(JSON 'null') AS result;

-- An error is thrown because `wide_number_mode` is case-sensitive and not "exact" or "round".
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'EXACT') as result;
SELECT FLOAT64(JSON '123.4', wide_number_mode=>'exac') as result;

-- An error is thrown because the number cannot be converted to DOUBLE without loss of precision
SELECT FLOAT64(JSON '18446744073709551615', wide_number_mode=>'exact') as result;

-- Returns a SQL NULL
SELECT SAFE.FLOAT64(JSON '"strawberry"') AS result;

JSON_TYPE

JSON_TYPE(json_expr)

Descrição

Usa uma expressão JSON e retorna o tipo do valor JSON mais externo como um SQL STRING. Os nomes desses tipos JSON podem ser retornados:

  • object
  • array
  • string
  • number
  • boolean
  • null

Se a expressão for SQL NULL, a função vai retornar SQL NULL. Se o valor JSON extraído não for um tipo JSON válido, ocorrerá um erro.

  • json_expr: JSON. Exemplo:

    JSON '{"name": "sky", "color" : "blue"}'
    

Tipo de retorno

STRING

Exemplos

SELECT json_val, JSON_TYPE(json_val) AS type
FROM
  UNNEST(
    [
      JSON '"apple"',
      JSON '10',
      JSON '3.14',
      JSON 'null',
      JSON '{"city": "New York", "State": "NY"}',
      JSON '["apple", "banana"]',
      JSON 'false'
    ]
  ) AS json_val;

+----------------------------------+---------+
| json_val                         | type    |
+----------------------------------+---------+
| "apple"                          | string  |
| 10                               | number  |
| 3.14                             | number  |
| null                             | null    |
| {"State":"NY","city":"New York"} | object  |
| ["apple","banana"]               | array   |
| false                            | boolean |
+----------------------------------+---------+

Codificações JSON

A tabela a seguir inclui codificações comuns usadas quando um valor SQL é codificado como valor JSON com a função TO_JSON_STRING ou TO_JSON.

Do SQL Para JSON Exemplos
NULL

null

Entrada SQL: NULL
Saída JSON: null
BOOL boolean Entrada SQL: TRUE
Saída JSON: true

Entrada SQL: FALSE
Saída JSON: false
INT64

(somente TO_JSON_STRING)

Número ou string

Codificado como um número quando o valor está no intervalo de [-253, 253], que é o intervalo de números inteiros que podem ser representados sem perdas como números de ponto flutuante de precisão dupla IEEE 754. Um valor fora desse intervalo é codificado como uma string.

Entrada SQL: 9007199254740992
Saída JSON: 9007199254740992

Entrada SQL: 9007199254740993
Saída JSON: "9007199254740993"
INT64

(somente TO_JSON)

Número ou string

Se o argumento stringify_wide_numbers for TRUE e o valor estiver fora do domínio do tipo FLOAT64, o valor será codificado como uma string. Se o valor não puder ser armazenado em JSON sem perda de precisão, a função falhará. Caso contrário, o valor é codificado como um número.

Caso o stringify_wide_numbers não seja usado ou seja FALSE, os valores numéricos fora do domínio do tipo "FLOAT64" não serão codificados como strings, mas serão armazenados como números JSON. Se um valor numérico não puder ser armazenado em JSON sem perda de precisão, um erro será gerado.

Entrada de SQL: 9007199254740992
Saída JSON: 9007199254740992

Entrada de SQL: 9007199254740993
Saída JSON: 9007199254740993

Entrada SQL com stringify_wide_numbers=>TRUE: 9007199254740992
Saída JSON: 9007199254740992

Entrada SQL com stringify_wide_numbers=>TRUE: 9007199254740993
Saída JSON: "9007199254740993"
NUMERIC
BIGNUMERIC

(somente TO_JSON_STRING)

Número ou string

Codificado como um número quando o valor está no intervalo de [-253, 253] e não tem parte fracionária. Um valor fora desse intervalo é codificado como uma string.

Entrada SQL: -1
Saída JSON: -1

Entrada SQL: 0
Saída JSON: 0

Entrada SQL: 9007199254740993
Saída JSON: "9007199254740993"

Entrada SQL: 123.56
Saída JSON: "123.56"
NUMERIC
BIGNUMERIC

(somente TO_JSON)

Número ou string

Se o argumento stringify_wide_numbers for TRUE e o valor estiver fora do domínio do tipo FLOAT64, ele será codificado como uma string. Caso contrário, ele será codificado como um número.

Entrada de SQL: -1
Saída JSON: -1

Entrada de SQL: 0
Saída JSON: 0

Entrada SQL: 9007199254740993
Saída JSON: 9007199254740993

Entrada SQL: 123.56
Saída JSON: 123.56

Entrada SQL com stringify_wide_numbers=>TRUE: 9007199254740993
Saída JSON: "9007199254740993"

Entrada SQL com stringify_wide_numbers=>TRUE: 123.56
Saída JSON: 123.56
FLOAT64

Número ou string

+/-inf e NaN são codificados como Infinity, -Infinity e NaN. Caso contrário, esse valor é codificado como um número.

Entrada SQL: 1.0
Saída JSON: 1

Entrada SQL: 9007199254740993
Saída JSON: 9007199254740993

Entrada SQL: "+inf"
Saída JSON: "Infinity"

Entrada SQL: "-inf"
Saída JSON: "-Infinity"

Entrada SQL: "NaN"
Saída JSON: "NaN"
STRING

string

Codificado como uma string, escapado de acordo com o padrão JSON. Especificamente, ", \ e os caracteres de controle de U+0000 a U+001F são escapados.

Entrada SQL: "abc"
Saída JSON: "abc"

Entrada SQL: "\"abc\""
Saída JSON: "\"abc\""
BYTES

string

Usa codificação de dados RFC 4648 Base64.

Entrada SQL: b"Google"
Saída JSON: "R29vZ2xl"
DATE string Entrada SQL: DATE '2017-03-06'
Saída JSON: "2017-03-06"
TIMESTAMP

string

Codificado como data e hora ISO 8601, em que T separa a data e hora e Z (Zulu/UTC) representa o fuso horário.

Entrada SQL: TIMESTAMP '2017-03-06 12:34:56.789012'
Saída JSON: "2017-03-06T12:34:56.789012Z"
DATETIME

string

Codificado como data e hora ISO 8601, em que T separa a data e hora.

Entrada SQL: DATETIME '2017-03-06 12:34:56.789012'
Saída JSON: "2017-03-06T12:34:56.789012"
TIME

string

Codificado como horário ISO 8601.

Entrada SQL: TIME '12:34:56.789012'
Saída JSON: "12:34:56.789012"
JSON

dados do JSON de entrada

Entrada SQL: JSON '{"item": "pen", "price": 10}'
Saída JSON: {"item":"pen", "price":10}

Entrada SQL:[1, 2, 3]
Saída JSON:[1, 2, 3]
ARRAY

matriz

Pode conter zero ou mais elementos.

Entrada SQL: ["red", "blue", "green"]
Saída JSON: ["red","blue","green"]

Entrada SQL:[1, 2, 3]
Saída JSON:[1,2,3]
STRUCT

objeto

O objeto pode conter zero ou mais pares de chave-valor. Cada valor é formatado de acordo com seu tipo.

Para TO_JSON, um campo é incluído na string de saída e quaisquer cópias desse campo são omitidas. Para TO_JSON_STRING, um campo e todas as cópias dele são incluídos na string de saída.

Campos anônimos são representados com "".

Nomes de campos UTF-8 inválidos podem resultar em JSON não analisáveis. 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.

Entrada SQL: STRUCT(12 AS purchases, TRUE AS inStock)
Saída JSON: {"inStock": true,"purchases":12}

Formato JSONPath

Com o formato JSONPath, é possível identificar os valores que você quer receber de uma string formatada em JSON. O formato JSONPath é compatível com estes operadores:

Operador Descrição Exemplos
$ Objeto ou elemento raiz. O formato JSONPath precisa começar com esse operador, que se refere ao nível mais externo da string formatada em JSON.

String formatada em JSON:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

Caminho de JSON:
"$"

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

. Operador filho. É possível identificar valores filhos usando uma notação de ponto.

String formatada em JSON:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

Caminho de JSON:
"$.class.students"

Resultado JSON:
[{"name":"Jane"}]

[] Operador subscrito. Se o objeto JSON for uma matriz, use colchetes para especificar o índice da matriz.

String formatada em JSON:
'{"class" : {"students" : [{"name" : "Jane"}]}}'

Caminho de JSON:
"$.class.students[0]"

Resultado JSON:
{"name":"Jane"}

Se uma chave em uma função JSON contiver um operador de formato JSON, consulte cada função JSON para saber como escapar.

Uma função JSON retornará NULL se o formato JSONPath não corresponder a um valor em uma string formatada em JSON. Se o valor selecionado para uma função escalar não for escalar, como um objeto ou uma matriz, a função retornará NULL. Se o formato JSONPath for inválido, ocorrerá um erro.