Trabalhar com dados JSON

Nesta página, descrevemos como trabalhar com JSON usando o Spanner.

Os dados do tipo JSON são semiestruturados e usados para armazenar dados JSON (JavaScript Object Notation). As especificações do formato JSON são descritas em RFC 7159 (em inglês).

O JSON é útil para complementar um esquema relacional para dados esparsos ou que com uma estrutura vagamente definida ou mutável. No entanto, o otimizador de consultas se baseia no modelo relacional para filtrar, juntar, agregar e classificar de maneira eficiente em escala. As consultas por JSON terão menos otimizações e recursos integrados para inspecionar e ajustar o desempenho.

Especificações

O tipo JSON do Spanner armazena uma representação normalizada do documento JSON de entrada.

  • O JSON pode ser aninhado para um máximo de 80 níveis.
  • O espaço em branco não é preservado.
  • Não é possível usar comentários. Transações ou consultas com comentários falharão.
  • Os membros de um objeto JSON são classificados lexicograficamente.
  • A ordem dos elementos da matriz JSON é preservada.
  • Se um objeto JSON tiver chaves duplicadas, apenas a primeira será preservada.
  • O tipo e o valor de tipos primitivos (string, booleano, número e nulo) serão preservados.
    • Os valores do tipo string serão preservados.
    • Os valores do tipo de número serão preservados, mas podem ter a representação textual como resultado do processo de normalização. Por exemplo, um número de entrada de 10000 pode ter uma representação normalizada de 1e+4. A semântica de preservação de valor numérico é a seguinte:
      • Os números inteiros assinados no intervalo de [INT64_MIN, INT64_MAX] são preservados.
      • Os números inteiros não assinados no intervalo de [0, UINT64_MAX] são preservados.
      • Valores duplicados que podem ser enviados entre string e duplicatas sem perda de precisão decimal são preservados. Se um valor de ponto flutuante de precisão dupla não puder ser enviado assim, a transação ou a consulta vai falhar.
        • Por exemplo, SELECT JSON '2.2412421353246235436' falha.
        • Uma solução funcional é PARSE_JSON('2.2412421353246235436', wide_number_mode=>'round'), que retorna JSON '2.2412421353246237'.
  • Use as funções TO_JSON(), JSON_OBJECT() e JSON_ARRAY() para criar documentos JSON em SQL. Essas funções implementam os caracteres de escape e de inclusão entre aspas necessários.

O tamanho máximo permitido do documento normalizado é de 10 MB.

Nulidade

Os valores JSON null são tratados como SQL não NULL.

Exemplo:

SELECT (JSON '{"a":null}').a IS NULL; -- Returns FALSE
SELECT (JSON '{"a":null}').b IS NULL; -- Returns TRUE

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

Codificação

Os documentos JSON precisam ser codificados em UTF-8. Transações ou consultas com documentos JSON codificados em outros formatos retornam um erro.

Criar uma tabela com colunas JSON

Uma coluna JSON pode ser adicionada a uma tabela quando ela é criada. Os valores do tipo JSON podem ser anuláveis.

CREATE TABLE Venues (
  VenueId   INT64 NOT NULL,
  VenueName  STRING(1024),
  VenueAddress STRING(1024),
  VenueFeatures JSON,
  DateOpened  DATE,
) PRIMARY KEY(VenueId);

Adicionar e remover colunas JSON de tabelas atuais

Também é possível adicionar e remover uma coluna JSON das tabelas atuais.

ALTER TABLE Venues ADD COLUMN VenueDetails JSON;
ALTER TABLE Venues DROP COLUMN VenueDetails;

O exemplo a seguir mostra como adicionar uma coluna JSON chamada VenueDetails à tabela Venues usando a CLI gcloud e as bibliotecas de cliente do Spanner.

gcloud

gcloud spanner databases ddl update DATABASE_ID \ --instance=INSTANCE_ID \
--ddl="ALTER TABLE Venues ADD COLUMN VenueDetails JSON;"

C++

void AddJsonColumn(google::cloud::spanner_admin::DatabaseAdminClient client,
                   std::string const& project_id,
                   std::string const& instance_id,
                   std::string const& database_id) {
  google::cloud::spanner::Database database(project_id, instance_id,
                                            database_id);
  auto metadata = client
                      .UpdateDatabaseDdl(database.FullName(), {R"""(
                        ALTER TABLE Venues ADD COLUMN VenueDetails JSON)"""})
                      .get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "`Venues` table altered, new DDL:\n" << metadata->DebugString();
}

C#


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class AddJsonColumnAsyncSample
{
    public async Task AddJsonColumnAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}"