Mit JSON-Daten arbeiten

Hinweis zur PostgreSQL-Schnittstelle: Der Datentyp JSON wird in PostgreSQL-Dialekt-Datenbanken nicht unterstützt. Verwenden Sie den Datentyp JSONB für PostgreSQL-Datenbanken mit Dialekt.

Auf dieser Seite wird beschrieben, wie Sie mit Spanner mit JSON arbeiten.

Der JSON-Datentyp ist ein semistrukturierter Datentyp, der zum Speichern von JSON-Daten (JavaScript Object Notation) verwendet wird. Die Spezifikationen für das JSON-Format werden in RFC 7159 beschrieben.

JSON ist nützlich, um ein relationales Schema für Daten zu ergänzen, die dünnbesetzt oder eine lose definierte oder sich ändernde Struktur haben. Das Abfrageoptimierungstool stützt sich jedoch auf das relationale Modell, um in großem Maßstab effizient zu filtern, zu joinen, zu aggregieren und zu sortieren. Abfragen über JSON haben weniger integrierte Optimierungen und weniger Kosten, um die Leistung zu prüfen und zu optimieren.

Spezifikationen

Der Spanner-JSON-Typ speichert eine normalisierte Darstellung des JSON-Eingabedokuments.

JSON kann auf maximal 80 Ebenen verschachtelt werden.
Leerzeichen bleiben nicht erhalten.
Kommentare werden nicht unterstützt. Transaktionen oder Abfragen mit Kommentaren schlagen fehl.
Mitglieder eines JSON-Objekts werden lexikografisch sortiert.
Die Reihenfolge der JSON-Arrayelemente wird beibehalten.
Wenn ein JSON-Objekt Schlüsselduplikate hat, wird nur der erste beibehalten.
Bei einfachen Typen (String, boolesch, Zahl und Null) werden Typ und Wert beibehalten.
- Die Werte der Stringtypen werden genau beibehalten.
- Zahlentypwerte bleiben erhalten, ihre Textdarstellung kann sich jedoch aufgrund des Normalisierungsprozesses jedoch ändern. Zum Beispiel kann eine Eingabenummer von 10.000 eine normalisierte Darstellung von 1e+4 haben. Folgendes ist die Semantik für die Beibehaltung der Zahl:
  - Signierte Ganzzahlen im Bereich von [INT64_MIN, INT64_MAX] bleiben erhalten.
  - Vorzeichenlose Ganzzahlen im Bereich von [0, UINT64_MAX] bleiben erhalten.
  - Doppelte Werte, bei denen ein String in eine Gleitkommazahl mit doppelter Genauigkeit umgewandelt wird, werden beibehalten. Wenn eine Gleitkommazahl mit doppelter Genauigkeit nicht auf diese Weise umgewandelt werden kann, schlägt die Transaktion oder Abfrage fehl.
    - Beispiel: SELECT JSON '2.2412421353246235436' schlägt fehl.
    - Eine funktionale Problemumgehung ist PARSE_JSON('2.2412421353246235436', wide_number_mode=>'round'), die JSON '2.2412421353246237' zurückgibt.

Die maximal zulässige Größe des normalisierten Dokuments ist 2.621.440 UTF-8-Zeichen.

Null-Zulässigkeit

JSON-null-Werte werden als SQL-Nicht-NULL behandelt.

Beispiel:

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

Codierung

JSON-Dokumente müssen in UTF-8 codiert sein. Transaktionen oder Abfragen mit JSON-Dokumenten, die in anderen Formaten codiert sind, geben einen Fehler zurück.

Tabelle mit JSON-Spalten erstellen

Eine JSON-Spalte kann beim Erstellen der Tabelle hinzugefügt werden. JSON-Werte können Nullwerte sein.

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

JSON-Spalten zu vorhandenen Tabellen hinzufügen und daraus entfernen

Eine JSON-Spalte kann auch einer vorhandenen Tabelle hinzugefügt und daraus entfernt werden.

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

Das folgende Beispiel zeigt, wie Sie der Tabelle Venues mithilfe der Spanner-Clientbibliotheken eine JSON-Spalte namens VenueDetails hinzufügen.

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.");
    }
}

Go


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

	database "cloud.google.com/go/spanner/admin/database/apiv1"
	adminpb "google.golang.org/genproto/googleapis/spanner/admin/database/v1"
)

// 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,
		Statements: []string{
			"ALTER TABLE Venues ADD COLUMN VenueDetails JSON",
		},
	})
	if err != nil {
		return err
	}
	if err := op.Wait(ctx); err != nil {
		return err
	}
	fmt.Fprintf(w, "Added VenueDetails column\n")
	return nil
}

Java


import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;
import com.google.common.collect.ImmutableList;
import com.google.spanner.admin.database.v1.DatabaseName;
import java.util.concurrent.ExecutionException;

class AddJsonColumnSample {

  static void addJsonColumn() throws InterruptedException, ExecutionException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my-project";
    String instanceId = "my-instance";
    String databaseId = "my-database";

    addJsonColumn(projectId, instanceId, databaseId);
  }

  static void addJsonColumn(String projectId, String instanceId, String databaseId)
      throws InterruptedException, ExecutionException {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      // Wait for the operation to finish.
      // This will throw an ExecutionException if the operation fails.
      databaseAdminClient.updateDatabaseDdlAsync(
          DatabaseName.of(projectId, instanceId, databaseId),
          ImmutableList.of("ALTER TABLE Venues ADD COLUMN VenueDetails JSON")).get();
      System.out.printf("Successfully added column `VenueDetails`%n");
    }
  }
}

Node.js


/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

// creates a client
const spanner = new Spanner({
  projectId: projectId,
});

const databaseAdminClient = spanner.getDatabaseAdminClient();

const request = ['ALTER TABLE Venues ADD COLUMN VenueDetails JSON'];

// Alter existing table to add a column.
const [operation] = await databaseAdminClient.updateDatabaseDdl({
  database: databaseAdminClient.databasePath(
    projectId,
    instanceId,
    databaseId
  ),
  statements: request,
});

console.log(`Waiting for operation on ${databaseId} to complete...`);

await operation.promise();

console.log(
  `Added VenueDetails column to Venues table in database ${databaseId}.`
);

PHP

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;
use Google\Cloud\Spanner\Admin\Database\V1\UpdateDatabaseDdlRequest;

/**
 * Adds a JSON column to a table.
 * Example:
 * ```
 * add_json_column($projectId, $instanceId, $databaseId);
 * ```
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function add_json_column(string $projectId, string $instanceId, string $databaseId): void
{
    $databaseAdminClient = new DatabaseAdminClient();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);

    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => ['ALTER TABLE Venues ADD COLUMN VenueDetails JSON']
    ]);

    $operation = $databaseAdminClient->updateDatabaseDdl($request);

    print('Waiting for operation to complete...' . PHP_EOL);
    $operation->pollUntilComplete();

    printf('Added VenueDetails as a JSON column in Venues table' . PHP_EOL);
}

Python

def add_json_column(instance_id, database_id):
    """Adds a new JSON column to the Venues table in the example database."""
    # instance_id = "your-spanner-instance"
    # database_id = "your-spanner-db-id"

    from google.cloud.spanner_admin_database_v1.types import \
        spanner_database_admin

    spanner_client = spanner.Client()
    database_admin_api = spanner_client.database_admin_api

    request = spanner_database_admin.UpdateDatabaseDdlRequest(
        database=database_admin_api.database_path(
            spanner_client.project, instance_id, database_id
        ),
        statements=["ALTER TABLE Venues ADD COLUMN VenueDetails JSON"],
    )

    operation = database_admin_api.update_database_ddl(request)

    print("Waiting for operation to complete...")
    operation.result(OPERATION_TIMEOUT_SECONDS)

    print(
        'Altered table "Venues" on database {} on instance {}.'.format(
            database_id, instance_id
        )
    )

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"
require "google/cloud/spanner/admin/database"

db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin

database_path = db_admin_client.database_path project: project_id,
                                              instance: instance_id,
                                              database: database_id

statements = ["ALTER TABLE Venues ADD COLUMN VenueDetails JSON"]
job = db_admin_client.update_database_ddl database: database_path,
                                          statements: statements
job.wait_until_done!

puts "Added VenueDetails column to Venues table in database #{database_id}"

JSON-Daten ändern

Im folgenden Beispiel wird gezeigt, wie Sie JSON-Daten mit den Spanner-Clientbibliotheken aktualisieren.