Funções JSON

O SQL do Cloud Spanner é compatível com as seguintes funções, 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 JSON, como uma string, número inteiro ou booleano. STRING com formatação JSON
JSON_VALUE Extrai um valor escalar. Um valor escalar pode representar uma string, um inteiro 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

JSON_QUERY(json_string_expr, json_path)

Descrição

Extrai um valor JSON, como uma matriz ou objeto, ou um valor escalar JSON, como uma string, um número inteiro 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"}]}}'
    

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

    SELECT JSON_QUERY("null", "$") -- Returns a SQL NULL
    
  • json_path: o JSONpath. Identifica os valores que você quer obter da string formatada em JSON.

    SELECT JSON_QUERY('{"a":null}', "$.a"); -- Returns a SQL NULL
    SELECT JSON_QUERY('{"a":null}', "$.b"); -- Returns a SQL NULL
    

Se você quiser incluir valores não escalares, como matrizes na extração, use JSON_QUERY. Se quiser apenas extrair valores escalares, como strings, números inteiros e valores booleanos, use JSON_VALUE.

Tipo de retorno

Um STRING formatado em JSON

Exemplos

Nos exemplos a seguir, os dados JSON são extraídos e retornados como strings formatadas como 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"}] |
+------------------------------------+

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])

Descrição

Extrai um valor escalar e o retorna como uma string. Um valor escalar pode representar uma string, um inteiro 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_path: o JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Se json_path retornar um valor JSON null ou não escalar (em outras palavras, se json_path se referir a um objeto ou uma matriz), um NULL SQL é retornado. Se esse parâmetro opcional não for fornecido, o símbolo $ do JSONPath será aplicado, o que significa que toda a string formatada em JSON será analisada.

Se quiser apenas extrair valores escalares, como strings, números inteiros e valores booleanos, use JSON_VALUE. Se você quiser incluir valores não escalares, como matrizes na extração, use JSON_QUERY.

Tipo de retorno

STRING

Exemplos

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

JSONPath

A maioria das funções JSON transmitem em um parâmetro json_string_expr e json_path. O parâmetro json_string_expr passa uma string formatada em JSON, e o parâmetro json_path identifica os valores que você quer receber da string formatada em JSON.

É necessário que o parâmetro json_string_expr seja uma string JSON formatada dessa forma:

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

O parâmetro json_path é construído usando o formato JSONPath. Como parte desse formato, é necessário que o parâmetro inicie com o símbolo $, que indica o nível mais externo da string formatada em JSON. É possível identificar valores filho usando pontos. Se o objeto JSON for uma matriz, use colchetes para especificar o índice da matriz. Se as chaves tiverem $, pontos ou colchetes, consulte sobre como inserir caracteres de escape para cada função JSON.

JSONPath Descrição Exemplo Resultado usando o json_string_expr acima
$ Objeto ou elemento raiz "$" {"class":{"students":[{"name":"Jane"}]}}
. Operador filho "$.class.students" [{"name":"Jane"}]
[] Operador subscrito "$.class.students[0]" {"name":"Jane"}

Uma função JSON retornará NULL se o parâmetro json_path não corresponder a um valor em json_string_expr. 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 JSONPath for inválido, a função gerará um erro.