Utilizzare i dati JSON

Questa pagina descrive come lavorare con i file JSON utilizzando Spanner.

Il tipo di dati JSON è un tipo di dati semistrutturato utilizzato per contenere dati JSON (JavaScript Object Notation). Le specifiche per il formato JSON sono descritte nel documento RFC 7159.

JSON è utile per integrare uno schema relazionale per i dati sparsi o con una struttura definita in modo approssimativo o in continua evoluzione. Tuttavia, l'ottimizzatore di query si basa sul modello relazionale per filtrare, unire, aggregare e ordinare in modo efficiente su larga scala. Le query su JSON avranno meno ottimizzazioni integrate e meno funzionalità per esaminare e ottimizzare le prestazioni.

Specifiche

Il tipo JSON di Spanner memorizza una rappresentazione normalizzata del documento JSON di input.

  • Il formato JSON può essere nidificato fino a un massimo di 80 livelli.
  • Lo spazio vuoto non viene mantenuto.
  • I commenti non sono supportati. Le transazioni o le query con commenti non andranno a buon fine.
  • I membri di un oggetto JSON sono ordinati in ordine lessicografico.
  • L'ordine degli elementi dell'array JSON viene mantenuto.
  • Se un oggetto JSON ha chiavi duplicate, viene conservata solo la prima.
  • I tipi primitivi (stringa, booleano, numero e null) mantengono il tipo e il valore.
    • I valori di tipo String vengono conservati esattamente.
    • I valori di tipo numerico vengono conservati, ma la loro rappresentazione testuale potrebbe essere modificata a seguito del processo di normalizzazione. Ad esempio, un numero di input pari a 10.000 potrebbe avere una rappresentazione normalizzata pari a 1e+4. La semantica di conservazione dei valori numerici è la seguente:
      • Gli interi con segno nell'intervallo [INT64_MIN, INT64_MAX] vengono conservati.
      • I numeri interi senza segno nell'intervallo [0, UINT64_MAX] vengono conservati.
      • I valori doppi che possono essere convertiti da stringa a doppio e da doppio a stringa senza perdita di precisione decimale vengono conservati. Se un valore doppio non può essere trasferito in questo modo, la transazione o la query non riesce.
        • Ad esempio, SELECT JSON '2.2412421353246235436' non va a buon fine.
        • Una soluzione alternativa funzionale è PARSE_JSON('2.2412421353246235436', wide_number_mode=>'round'), che restituisce JSON '2.2412421353246237'.
  • Utilizza le funzioni TO_JSON(), JSON_OBJECT() e JSON_ARRAY() per creare documenti JSON in SQL. Queste funzioni implementano i caratteri di escape e le virgolette necessari.

La dimensione massima consentita del documento normalizzato è di 10 MB.

Supporto di valori Null

I valori JSON null vengono trattati come SQL non NULL.

Ad esempio:

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

I documenti JSON devono essere codificati in UTF-8. Le transazioni o le query con documenti JSON codificati in altri formati restituiscono un errore.

Crea una tabella con colonne JSON

Una colonna JSON può essere aggiunta a una tabella quando viene creata. I valori del tipo JSON possono essere nullabili.

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

Aggiungere e rimuovere colonne JSON dalle tabelle esistenti

Una colonna JSON può anche essere aggiunta e rimossa dalle tabelle esistenti.

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

L'esempio seguente mostra come aggiungere una colonna JSON denominata VenueDetails alla tabella Venues utilizzando gcloud CLI e le librerie client di 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}";
        string alterStatement = "ALTER TABLE Venues ADD COLUMN VenueDetails JSON";

        using var connection = new SpannerConnection(connectionString);
        using var updateCmd = connection.CreateDdlCommand(alterStatement);
        await updateCmd.ExecuteNonQueryAsync();
        Console.WriteLine("Added the VenueDetails column.");
    }
}

Vai


import (
	"context"
	"fmt"
	"io"
	"regexp"

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
)

// addJsonColumn creates a column in the database of type JSON
func addJsonColumn(w io.Writer, db string) error {
	// db = `projects/<project>/instances/<instance-id>/database/<database-id>`
	matches := regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)
	if matches == nil || len(matches) != 3 {
		return fmt.Errorf("addJsonColumn: invalid database id %s", db)
	}

	ctx := context.Background()
	adminClient, err := database.NewDatabaseAdminClient(ctx)
	if err != nil {
		return err
	}
	defer adminClient.Close()

	op, err := adminClient.UpdateDatabaseDdl(ctx, &adminpb.UpdateDatabaseDdlRequest{
		Database: db