Funções JSON

O Cloud Spanner SQL é 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_QUERY ou JSON_VALUE

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

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

Descrição

Extrai valores JSON ou valores escalares JSON como strings.

  • json_string_expr: uma string formatada em JSON. Exemplo:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_string_literal: formato JSONpath. Identifica os valores que você quer obter da string formatada em JSON. Se json_path_string_literal retornar um null JSON, isso será convertido em um NULL SQL.

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

Exemplos

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('{ "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;

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

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

Formato JSONPath

A maioria das funções JSON transmitem em um parâmetro json_string_expr e json_path_string_literal. O parâmetro json_string_expr transmite uma string formatada em JSON, e o parâmetro json_path_string_literal identifica os valores que você quer obter 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_string_literal é 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_string_literal 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.