Mit JSONB-Daten arbeiten

Auf dieser Seite wird beschrieben, wie Sie mit dem Datentyp JSONB arbeiten, wenn Sie Spanner verwenden.

JSONB ist ein PostgreSQL-Datentyp, der im Spanner-PostgreSQL-Dialekt zum Speichern semistrukturierter Daten verwendet wird. JSONB enthält Daten im JSON-Format (JavaScript Object Notation), das der in RFC 7159 beschriebenen Spezifikation entspricht.

Spezifikationen

Der Spanner-Datentyp JSONB speichert eine normalisierte Darstellung des Eingabedokuments. Das bedeutet Folgendes:

  • Anführungszeichen und Leerzeichen werden nicht beibehalten.
  • Kommentare werden nicht unterstützt. Transaktionen oder Abfragen mit Kommentaren schlagen fehl.
  • Objektschlüssel werden zuerst nach Schlüssellänge und dann lexikografisch nach der entsprechenden Objektschlüssellänge sortiert. Wenn es doppelte Objektschlüssel gibt, wird nur der letzte beibehalten.
  • Bei einfachen Typen (string, boolean, number und null) bleiben Typ und Wert erhalten.
    • Werte vom Typ string werden exakt beibehalten.
    • Nachgestellte Nullen bleiben erhalten. Das Ausgabeformat für Werte vom Typ number verwendet keine wissenschaftliche Notation.

  • JSONB-null-Werte werden als SQL-Nicht-NULL behandelt. Beispiel:

    SELECT null::jsonb IS NULL;  -- Returns true
SELECT 'null'::jsonb IS NULL; -- Returns false

SELECT '{"a":null}'::jsonb -> 'a' IS NULL; -- Returns false
SELECT '{"a":null}'::jsonb -> 'b' IS NULL; -- Returns true

SELECT '{"a":null}'::jsonb -> 'a'; -- Returns a JSONB 'null'
SELECT '{"a":null}'::jsonb -> 'b'; -- Returns a SQL NULL

  • Die Reihenfolge der JSONB-Array-Elemente bleibt erhalten.

Beschränkungen

Für Spanner JSONB gelten die folgenden Einschränkungen:

  • Argumente für die Funktion to_jsonb können nur von den PostgreSQL-Datentypen stammen, die von Spanner unterstützt werden.
  • Werte vom Typ „Number“ können 4.932 Ziffern vor dem Dezimaltrennzeichen und 16.383 Ziffern nach dem Dezimaltrennzeichen haben.
  • Die maximal zulässige Größe des normalisierten Speicherformats beträgt 10 MB.
  • JSONB-Dokumente müssen UTF-8-codiert sein. Bei Transaktionen oder Abfragen mit JSONB-Dokumenten, die in anderen Formaten codiert sind, wird ein Fehler zurückgegeben.

Vergleich und Sortierung

Sie können JSONB-Werte auf Gleichheit und Reihenfolge vergleichen. Sie können JSONB-Werte auch in einer GROUP BY- und ORDER BY-Klausel verwenden.

Beim Vergleich von JSONB-Werten unterschiedlicher Typen gelten die folgenden Beziehungen:

Null (JSONB null) < String < Number < Boolean < Array < Object

Beim Vergleich von JSONB-Werten desselben Typs gelten die folgenden Regeln:

  • Objekte: Objekte mit mehr Schlüssel/Wert-Paaren sind größer als Objekte mit weniger Paaren. Wenn beide Objekte die gleiche Anzahl von Paaren haben, werden ihre Schlüssel/Wert-Paare paarweise (zuerst Schlüssel, dann Werte) in der in den Spezifikationen definierten Schlüsselreihenfolge verglichen. Das erste Paar, das sich unterscheidet, bestimmt die Reihenfolge.
  • Arrays: Arrays mit mehr Elementen sind größer als Arrays mit weniger Elementen. Wenn beide Arrays dieselbe Anzahl von Elementen haben, werden die Elemente von links nach rechts Index für Index verglichen. Das erste abweichende Element bestimmt die Array-Reihenfolge.
  • Boolesche Werte: true ist größer als false.
  • Zahlen: Mathematischer Vergleich anhand von numerischen Werten.
  • Strings: Werden lexikografisch anhand von Standardregeln für den Stringvergleich verglichen.

  • JSONB-Nullwerte: Alle JSONB-null-Werte sind gleich und kleiner als alle anderen JSON-Werte, die nicht null sind. Es gibt jedoch einen Grenzfall:

    • Ein leeres Array der obersten Ebene [] wird als kleiner als ein JSONB-Nullwert betrachtet. Da dies nur für Arrays der obersten Ebene gilt, geben die folgenden Anweisungen „true“ zurück:

      • '[]'::jsonb < 'null'::jsonb
      • 'null'::jsonb < '[[]]'::jsonb
      • '[null]'::jsonb < '[[]]'::jsonb

Tabelle mit JSONB-Spalten erstellen

Sie können einer Tabelle beim Erstellen eine JSONB-Spalte hinzufügen.

CREATE TABLE Venues (
 VenueId   BIGINT PRIMARY KEY,
 VenueName  VARCHAR(1024),
 VenueAddress VARCHAR(1024),
 VenueFeatures JSONB,
 DateOpened  TIMESTAMPTZ
);

Hier ein Beispiel für ein VenueFeatures-JSONB-Objekt:

{
    "rating": 4.5,
    "capacity":"1500",
    "construction":"brick",
    "tags": [
        "multi-cuisine",
        "open-seating",
        "stage",
        "public address system"
    ]
}

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

Sie können eine JSONB-Spalte hinzufügen und mit ALTER-Anweisungen so löschen:

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

Im folgenden Beispiel wird gezeigt, wie Sie mithilfe von Spanner-Clientbibliotheken eine JSONB-Spalte mit dem Namen VenueDetails zur Tabelle Venues hinzufügen.

C++

void JsonbAddColumn(google::cloud::spanner_admin::DatabaseAdminClient client,
                    google::cloud::spanner::Database const& database) {
  std::vector<std::string> statements = {
      R"""(
        ALTER TABLE Venues
            ADD COLUMN VenueDetails JSONB
      )""",
  };
  auto metadata =
      client.UpdateDatabaseDdl(database.FullName(), statements).get();
  if (!metadata) throw std::move(metadata).status();
  std::cout << "Added JSONB column to table Venues in database "
            << database.FullName() << "\nNew DDL:\n"
            << metadata->DebugString();
}

C#


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

public class AddJsonbColumnAsyncPostgresSample
{
    public async Task AddJsonbColumnAsyncPostgres(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        string alterStatement = $"ALTER TABLE VenueDetails ADD COLUMN Details JSONB";

        using var connection = new SpannerConnection(connectionString);
        using var ddlCmd = connection.CreateDdlCommand(alterStatement);
        await ddlCmd.ExecuteNonQueryAsync();
        Console.WriteLine($"Added the JSONB column named Details to VenueDetails table.");
    }
}

Go


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

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

// addJsonBColumn creates a column in the database of type JSONB
func addJsonBColumn(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("addJsonbColumn: 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 JSONB",
		},
	})
	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 AddJsonbColumnSample {

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

    addJsonbColumn(projectId, instanceId, databaseId);
  }

  static void addJsonbColumn(String projectId, String instanceId, String databaseId)
      throws InterruptedException, ExecutionException {
    try (Spanner spanner =
        SpannerOptions.newBuilder()
            .setProjectId(projectId)
            .build()
            .getService();
        DatabaseAdminClient databaseAdminClient = spanner.createDatabaseAdminClient()) {
      // JSONB datatype is only supported with PostgreSQL-dialect databases.
      // 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 JSONB")).get();
      System.out.printf("Successfully added column `VenueDetails`%n");
    }
  }
}

Node.js

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

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

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

async function pgJsonbAddColumn() {
  // Gets a reference to a Cloud Spanner Database Admin Client object
  const databaseAdminClient = spanner.getDatabaseAdminClient();

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

  // Updates schema by adding a new table.
  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 jsonb column to table venues to database ${databaseId}.`,
  );
}
pgJsonbAddColumn();

PHP

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

/**
 * Add a JSONB column to a table present in a PG Spanner database.
 *
 * @param string $projectId The Google Cloud project ID.
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $tableName The table in which the column needs to be added.
 */
function pg_add_jsonb_column(
    string $projectId,
    string $instanceId,
    string $databaseId,
    string $tableName = 'Venues'
): void {
    $databaseAdminClient = new DatabaseAdminClient();
    $databaseName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);
    $statement = sprintf('ALTER TABLE %s ADD COLUMN VenueDetails JSONB', $tableName);
    $request = new UpdateDatabaseDdlRequest([
        'database' => $databaseName,
        'statements' => [$statement]
    ]);

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

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

    print(sprintf('Added column VenueDetails on table %s.', $tableName) . PHP_EOL);
}

Python

def add_jsonb_column(instance_id, database_id):
    """
    Alters Venues tables in the database adding a JSONB column.
    You can create the table by running the `create_table_with_datatypes`
    sample or by running this DDL statement against your database:
    CREATE TABLE Venues (
      VenueId         BIGINT NOT NULL,
      VenueName       character varying(100),
      VenueInfo       BYTEA,
      Capacity        BIGINT,
      OutdoorVenue    BOOL,
      PopularityScore FLOAT8,
      Revenue         NUMERIC,
      LastUpdateTime  SPANNER.COMMIT_TIMESTAMP NOT NULL,
      PRIMARY KEY (VenueId))
    """
    # 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 JSONB"],
    )

    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

require "google/cloud/spanner"

def spanner_postgresql_jsonb_add_column project_id:, instance_id:, database_id:
  # project_id  = "Your Google Cloud project ID"
  # instance_id = "Your Spanner instance ID"
  # database_id = "Your Spanner database ID"


  # Show how to add JSONB column
  db_admin_client = Google::Cloud::Spanner::Admin::Database.database_admin project: project_id

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

  add_column_query = "ALTER TABLE Venues ADD COLUMN VenueDetails JSONB"

  job = db_admin_client.update_database_ddl database: db_path,
                                            statements: [add_column_query]

  job.wait_until_done!

  if job.error?
    puts "Error while adding column. Code: #{job.error.code}. Message: #{job.error.message}"
    raise GRPC::BadStatus.new(job.error.code, job.error.message)
  end

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

JSONB-Daten ändern

Sie können eine JSONB-Spalte wie jede andere Spalte ändern.

Hier ein Beispiel:

UPDATE Venues SET VenueFeatures = '{"rating": 4.5, "tags":["multi-cuisine", "open-seating"] }'
  WHERE VenueId = 1;

Im folgenden Beispiel wird gezeigt, wie Sie JSONB-Daten mithilfe von Spanner-Clientbibliotheken aktualisieren.

C++

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

void JsonbUpdateData(google::cloud::spanner::Client client) {
  auto venue19_details = google::cloud::spanner::JsonB(R"""(
        {"rating": 9, "open": true}
      )""");
  // PG.JSONB takes the last value in the case of duplicate keys.
  auto venue4_details = google::cloud::spanner::JsonB(R"""(
        [
          {"name": null, "available": true},
          {"name": "room 2", "available": false, "name": "room 3"},
          {
            "main hall": {
              "description": "this is the biggest space",
              "size": 200
            }
          }
        ]
      )""");
  auto venue42_details = google::cloud::spanner::JsonB(R"""(
        {
          "name": null,
          "open": {"Monday": true, "Tuesday": false},
          "tags": ["large", "airy"]
        }
      )""");
  auto update_venues = google::cloud::spanner::InsertOrUpdateMutationBuilder(
                           "Venues", {"VenueId", "VenueDetails"})
                           .EmplaceRow(19, venue19_details)
                           .EmplaceRow(4, venue4_details)
                           .EmplaceRow(42, venue42_details)
                           .Build();
  auto commit_result =
      client.Commit(google::cloud::spanner::Mutations{update_venues});
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Updated data.\n";
}

C#

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 


using Google.Cloud.Spanner.Data;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

public class UpdateDataWithJsonbAsyncPostgresSample
{
    public async Task UpdateDataWithJsonbAsyncPostgres(string projectId, string instanceId, string databaseId)
    {
        List<VenueInformation> venueInformationList = new List<VenueInformation>
        {
            // If you are using .NET Core 3.1 or later, you can use System.Text.Json for serialization instead.
            new VenueInformation
            {
                VenueId = 19,
                Details = JsonConvert.SerializeObject(new
                {
                    rating = 9,
                    open = true,
                })
            },
            new VenueInformation
            {
                VenueId = 4,
                // In the case of repeated field names in the JSON, PostgreSQL JSONB will keep the value of the last field appearance.
                // For instance, in the following example, the value for name will be room 3.
                Details = @"
                {
                    ""name"": ""room 2"",
                    ""available"": false,
                    ""name"": ""room 3""
                }"
            },
            new VenueInformation
            {
                VenueId = 42,
                Details = JsonConvert.SerializeObject(new
                {
                    name = "Central Park",
                    open = new
                    {
                        Monday = true,
                        Tuesday = false,
                    },
                    tags = new string[] {"large", "airy" },
                }),
            },
        };
        // Create connection to Cloud Spanner.
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        await Task.WhenAll(venueInformationList.Select(venue =>
        {
            // Update rows in the Venues table.
            using var cmd = connection.CreateUpdateCommand("VenueInformation", new SpannerParameterCollection
            {
                { "VenueId", SpannerDbType.Int64, venue.VenueId },
                { "Details", SpannerDbType.PgJsonb, venue.Details }
            });
            return cmd.ExecuteNonQueryAsync();
        }));
        Console.WriteLine("Data updated.");
    }

    public struct VenueInformation
    {
        public int VenueId { get; set; }
        public string Details { get; set; }
    }
}

Go

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 


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

	"cloud.google.com/go/spanner"
)

// updateDataWithJsonBColumn updates database with JsonB type values
func updateDataWithJsonBColumn(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("updateDataWithJsonBColumn: invalid database id %s", db)
	}

	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	type VenueDetails struct {
		Name   spanner.NullString   `json:"name"`
		Rating spanner.NullFloat64  `json:"rating"`
		Open   interface{}          `json:"open"`
		Tags   []spanner.NullString `json:"tags"`
	}

	details_1 := spanner.PGJsonB{Value: []VenueDetails{
		{Name: spanner.NullString{StringVal: "room1", Valid: true}, Open: true},
		{Name: spanner.NullString{StringVal: "room2", Valid: true}, Open: false},
	}, Valid: true}
	details_2 := spanner.PGJsonB{Value: VenueDetails{
		Rating: spanner.NullFloat64{Float64: 9, Valid: true},
		Open:   true,
	}, Valid: true}

	details_3 := spanner.PGJsonB{Value: VenueDetails{
		Name: spanner.NullString{Valid: false},
		Open: map[string]bool{"monday": true, "tuesday": false},
		Tags: []spanner.NullString{{StringVal: "large", Valid: true}, {StringVal: "airy", Valid: true}},
	}, Valid: true}

	cols := []string{"VenueId", "VenueDetails"}
	_, err = client.Apply(ctx, []*spanner.Mutation{
		spanner.Update("Venues", cols, []interface{}{4, details_1}),
		spanner.Update("Venues", cols, []interface{}{19, details_2}),
		spanner.Update("Venues", cols, []interface{}{42, details_3}),
	})

	if err != nil {
		return err
	}
	fmt.Fprintf(w, "Updated data to VenueDetails column\n")

	return nil
}

Java

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.Mutation;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Value;
import com.google.common.collect.ImmutableList;

class UpdateJsonbDataSample {

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

    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
      DatabaseClient client =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      updateJsonbData(client);
    }
  }

  static void updateJsonbData(DatabaseClient client) {
    // PG JSONB takes the last value in the case of duplicate keys.
    // PG JSONB sorts first by key length and then lexicographically with
    // equivalent key length.
    client.write(
        ImmutableList.of(
            Mutation.newInsertOrUpdateBuilder("Venues")
                .set("VenueId")
                .to(4L)
                .set("VenueDetails")
                .to(
                    Value.pgJsonb(
                        "[{\"name\":\"room 1\",\"open\":true,\"name\":\"room 3\"},"
                            + "{\"name\":\"room 2\",\"open\":false}]"))
                .build(),
            Mutation.newInsertOrUpdateBuilder("Venues")
                .set("VenueId")
                .to(19L)
                .set("VenueDetails")
                .to(Value.pgJsonb("{\"rating\":9,\"open\":true}"))
                .build(),
            Mutation.newInsertOrUpdateBuilder("Venues")
                .set("VenueId")
                .to(42L)
                .set("VenueDetails")
                .to(
                    Value.pgJsonb(
                        "{\"name\":null,"
                            + "\"open\":{\"Monday\":true,\"Tuesday\":false},"
                            + "\"tags\":[\"large\",\"airy\"]}"))
                .build()));
    System.out.println("Venues successfully updated");
  }
}

Node.js

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

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

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

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

async function pgJsonbUpdateData() {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);
  // Instantiate Spanner table objects.
  const venuesTable = database.table('venues');

  const data = [
    {
      VenueId: '19',
      VenueDetails: {rating: 9, open: true},
    },
    {
      VenueId: '4',
      // PG JSONB sorts first by key length and then lexicographically with equivalent key length
      // and takes the last value in the case of duplicate keys
      VenueDetails: `[
      {
        "name": null,
        "available": true
      },
      {
        "name": "room 2",
        "available": false,
        "name": "room 3"
      },
      {
        "main hall": {
          "description": "this is the biggest space",
          "size": 200
        }
      }
    ]`,
    },
    {
      VenueId: '42',
      VenueDetails: {
        name: null,
        open: {
          Monday: true,
          Tuesday: false,
        },
        tags: ['large', 'airy'],
      },
    },
  ];

  try {
    await venuesTable.update(data);
    console.log('Updated data.');
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    await database.close();
  }
}
pgJsonbUpdateData();

PHP

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

use Google\Cloud\Spanner\SpannerClient;

/**
 * Insert/update data in a JSONB column in a Postgres table.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $tableName The table in which the data needs to be updated.
 */
function pg_jsonb_update_data(
    string $instanceId,
    string $databaseId,
    string $tableName = 'Venues'
): void {
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->insertOrUpdateBatch($tableName, [
        [
            'VenueId' => 1,
            'VenueDetails' => '{"rating": 9, "open": true}'
        ],
        [
            'VenueId' => 4,
            'VenueDetails' => '[
                {
                    "name": null,
                    "available": true
                },' .
                // PG JSONB sorts first by key length and then lexicographically with
                // equivalent key length and takes the last value in the case of duplicate keys
                '{
                    "name": "room 2",
                    "available": false,
                    "name": "room 3"
                },
                {
                    "main hall": {
                        "description": "this is the biggest space",
                        "size": 200
                    }
                }
            ]'
        ],
        [
            'VenueId' => 42,
            'VenueDetails' => $spanner->pgJsonb([
                'name' => null,
                'open' => [
                    'Monday' => true,
                    'Tuesday' => false
                ],
                'tags' => ['large', 'airy'],
            ])
        ]
    ]);

    print(sprintf('Inserted/updated 3 rows in table %s', $tableName) . PHP_EOL);
}

Python

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

def update_data_with_jsonb(instance_id, database_id):
    """Updates Venues tables in the database with the JSONB
    column.
    This updates the `VenueDetails` column which must be created before
    running this sample. You can add the column by running the
    `add_jsonb_column` sample or by running this DDL statement
     against your database:
        ALTER TABLE Venues ADD COLUMN VenueDetails JSONB
    """
    # instance_id = "your-spanner-instance"
    # database_id = "your-spanner-db-id"

    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    """
    PG JSONB takes the last value in the case of duplicate keys.
    PG JSONB sorts first by key length and then lexicographically with
    equivalent key length.
    """

    with database.batch() as batch:
        batch.update(
            table="Venues",
            columns=("VenueId", "VenueDetails"),
            values=[
                (
                    4,
                    JsonObject(
                        [
                            JsonObject({"name": None, "open": True}),
                            JsonObject({"name": "room 2", "open": False}),
                        ]
                    ),
                ),
                (19, JsonObject(rating=9, open=True)),
                (
                    42,
                    JsonObject(
                        {
                            "name": None,
                            "open": {"Monday": True, "Tuesday": False},
                            "tags": ["large", "airy"],
                        }
                    ),
                ),
            ],
        )

    print("Updated data.")

Ruby

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

require "google/cloud/spanner"

def spanner_postgresql_jsonb_update_data project_id:, instance_id:, database_id:
  # project_id  = "Your Google Cloud project ID"
  # instance_id = "Your Spanner instance ID"
  # database_id = "Your Spanner database ID"

  # Insert JSONB data into table
  spanner = Google::Cloud::Spanner.new project: project_id
  client  = spanner.client instance_id, database_id

  data = [
    {
      VenueId: "19",
      VenueDetails: { rating: 9, open: true }
    },
    {
      VenueId: "4",
      VenueDetails: [
        {
          name: null,
          open: true
        },
        {
          name: "room 2",
          open: false
        },
        {
          main_hall: {
            description: "this is the biggest space",
            size: 200
          }
        }
      ]
    },
    {
      VenueId: "42",
      VenueDetails: {
        name: null,
        open: {
          Monday: true,
          Tuesday: false
        },
        tags: ["large", "airy"]
      }
    }
  ]

  client.upsert "Venues", data
  puts "Inserted data into Venues table"
end

JSON-Daten indexieren

Sie können die Abfrage von JSONB-Daten beschleunigen, indem Sie sekundäre Indexe und Suchindexe mit Ihren JSONB-Daten verwenden. Spanner unterstützt die Verwendung von JSONB-Spalten als Schlüssel in sekundären Indexen nicht.

Sekundären Index verwenden

Sekundäre Indexe sind nützlich, wenn Sie nach skalaren Werten in einem JSONB-Dokument filtern. Wenn Sie sekundäre Indexe mit JSONB verwenden möchten, erstellen Sie eine generierte Spalte, die die relevanten skalaren Daten extrahiert und in einen geeigneten SQL-Datentyp umwandelt. Anschließend können Sie einen sekundären Index für diese generierte Spalte erstellen. Der Index beschleunigt zulässige Abfragen, die für die generierte Spalte ausgeführt werden.

Im folgenden Beispiel erstellen Sie einen VenuesByCapacity-Index, mit dem die Datenbank die Veranstaltungsorte mit einer Kapazität von mehr als 1.000 findet. Anstatt jede Zeile zu prüfen, verwendet Spanner den Index, um die relevanten Zeilen zu finden. Dadurch wird die Abfrageleistung verbessert, insbesondere bei großen Tabellen.

ALTER TABLE Venues (
ADD COLUMN VenueCapacity BIGINT GENERATED ALWAYS AS ((VenueFeatures->>'capacity')::BIGINT) VIRTUAL,
DateOpened TIMESTAMPTZ
);

CREATE INDEX VenuesByCapacity ON Venues(VenueCapacity);

SELECT VenueName
FROM Venues
WHERE VenueCapacity > 1000;

Suchindexe verwenden

Suchindexe sind nützlich, wenn Sie Abfragen für JSONB-Dokumente ausführen, die dynamisch oder unterschiedlich sind. Im Gegensatz zu sekundären Indexen können Sie Suchindexe für jedes JSONB-Dokument erstellen, das in einer JSONB-Spalte gespeichert ist. Der Suchindex passt sich automatisch an Variationen in JSON-Dokumenten, zwischen verschiedenen Zeilen und im Laufe der Zeit an.

Im folgenden Beispiel erstellen Sie einen VenuesByVenueDetails-Suchindex, mit dem die Datenbank Veranstaltungsorte mit bestimmten Details wie Größe und Betriebszeiten findet. Anstatt jede Zeile zu prüfen, verwendet Spanner den Index, um die relevanten Zeilen zu finden. Das verbessert die Abfrageleistung, insbesondere bei großen Tabellen.

ALTER TABLE Venues
ADD COLUMN VenueDetails_Tokens spanner.tokenlist
  GENERATED ALWAYS AS (spanner.tokenize_jsonb(VenueDetails)) VIRTUAL HIDDEN;

CREATE SEARCH INDEX VenuesByVenueDetails
ON Venues (VenueDetails_Tokens);

SELECT VenueName
FROM Venues
WHERE VenueDetails @> '{"labels": ["large"], "open": {"Friday": true}}'::jsonb;

SELECT VenueName
FROM Venues
WHERE VenueDetails->'open'->'Friday' = 'true'::jsonb
  AND VenueDetails->'type' IN ('"stadium"'::jsonb, '"arena"'::jsonb);

Weitere Informationen finden Sie unter JSON-Suchindexe.

JSONB-Daten abfragen

Sie können JSONB-Spalten anhand der Werte der zugrunde liegenden Felder abfragen. Im folgenden Beispiel werden VenueId und VenueName aus Venues extrahiert, wobei VenueFeatures einen rating-Wert größer als 3.5 hat.

SELECT VenueId, VenueName
FROM Venues
WHERE (VenueFeatures->>'rating')::FLOAT8 > 3.5;

Im folgenden Beispiel wird gezeigt, wie Sie JSONB-Daten mithilfe von Spanner-Clientbibliotheken abfragen.

C++

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

void JsonbQueryWithParameter(google::cloud::spanner::Client client) {
  auto sql = google::cloud::spanner::SqlStatement(
      "SELECT VenueId, VenueDetails FROM Venues"
      "  WHERE CAST(VenueDetails ->> 'rating' AS INTEGER) > $1",
      {{"p1", google::cloud::spanner::Value(2)}});
  using RowType =
      std::tuple<std::int64_t, std::optional<google::cloud::spanner::JsonB>>;
  auto rows = client.ExecuteQuery(std::move(sql));
  for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
    if (!row) throw std::move(row).status();
    std::cout << "VenueId: " << std::get<0>(*row) << ", ";
    std::cout << "Details: " << std::string(std::get<1>(*row).value()) << "\n";
  }
}

C#

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 


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

public class QueryJsonbDataUsingParameterAsyncPostgresSample
{
    public async Task<List<VenueInformation>> QueryJsonbDataUsingParameterAsyncPostgres(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
        using var connection = new SpannerConnection(connectionString);
        // Get all the venues with a rating greater than 2.
        /* Details is a column of type JSONB. Some of the data persisted in the Details column has the following structure:
           [{
                 "name": "string",
                 "available": true,
                 "rating": int // This field is optional.
           }] */
        using var command = connection.CreateSelectCommand(
            "SELECT venueid, details FROM VenueInformation WHERE CAST(details ->> 'rating' AS INTEGER) > $1",
            new SpannerParameterCollection
            {
                { "p1", SpannerDbType.Int64, 2 }
            });
        var venues = new List<VenueInformation>();
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            venues.Add(new VenueInformation
            {
                VenueId = reader.GetFieldValue<int>("venueid"),
                Details = reader.GetFieldValue<string>("details")
            });
        }
        return venues;
    }

    public struct VenueInformation
    {
        public int VenueId { get; set; }
        public string Details { get; set; }
    }
}

Go

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 


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

	"cloud.google.com/go/spanner"
	"google.golang.org/api/iterator"
)

// queryWithJsonBParameter queries data on the JSON type column of the database
func queryWithJsonBParameter(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("queryWithJsonBParameter: invalid database id %s", db)
	}
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	type VenueDetails struct {
		Name   spanner.NullString   `json:"name"`
		Rating spanner.NullFloat64  `json:"rating"`
		Open   interface{}          `json:"open"`
		Tags   []spanner.NullString `json:"tags"`
	}

	stmt := spanner.Statement{
		SQL: `SELECT VenueId, VenueDetails FROM Venues WHERE CAST(venuedetails ->> 'rating' AS INTEGER) > $1`,
		Params: map[string]interface{}{
			"p1": 2,
		},
	}
	iter := client.Single().Query(ctx, stmt)
	defer iter.Stop()
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}
		var venueID int64
		var venueDetails spanner.PGJsonB
		if err := row.Columns(&venueID, &venueDetails); err != nil {
			return err
		}
		fmt.Fprintf(w, "The venue details for venue id %v is %v\n", venueID, venueDetails)
	}
}

Java

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
import com.google.cloud.spanner.Value;

class QueryWithJsonbParameterSample {

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

    try (Spanner spanner =
        SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
      DatabaseClient client =
          spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
      queryWithJsonbParameter(client);
    }
  }

  static void queryWithJsonbParameter(DatabaseClient client) {
    int rating = 2;
    Statement statement =
        Statement.newBuilder(
                "SELECT VenueId, VenueDetails\n"
                    + "FROM Venues\n"
                    + "WHERE CAST(venuedetails ->> 'rating' "
                    + "AS INTEGER) > $1")
            .bind("p1")
            .to(Value.int64(rating))
            .build();
    try (ResultSet resultSet = client.singleUse().executeQuery(statement)) {
      while (resultSet.next()) {
        System.out.printf(
            "VenueId: %s, VenueDetails: %s%n",
            resultSet.getLong("venueid"), resultSet.getPgJsonb("venuedetails"));
      }
    }
  }
}

Node.js

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

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

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

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

async function pgJsonbDataType() {
  // Gets a reference to a Cloud Spanner instance and database.
  const instance = spanner.instance(instanceId);
  const database = instance.database(databaseId);

  const select_query = {
    sql: `SELECT venueid, venuedetails FROM Venues
        WHERE CAST(venuedetails ->> 'rating' AS INTEGER) > $1`,
    params: {
      p1: 2,
    },
    types: {
      p1: 'int64',
    },
    json: true,
  };

  // Queries row from the Venues table.
  try {
    const [rows] = await database.run(select_query);

    rows.forEach(row => {
      console.log(
        `VenueId: ${row.venueid}, Details: ${JSON.stringify(
          row.venuedetails,
        )}`,
      );
    });
  } finally {
    // Close the database when finished.
    await database.close();
  }
}
pgJsonbDataType();

PHP

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 


use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\SpannerClient;

/**
 * Query data to a jsonb column in a PostgreSQL table.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @param string $tableName The table from which the data needs to be queried.
 */
function pg_jsonb_query_parameter(
    string $instanceId,
    string $databaseId,
    string $tableName = 'Venues'
): void {
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $results = $database->execute(
        sprintf('SELECT venueid, venuedetails FROM %s', $tableName) .
        " WHERE CAST(venuedetails ->> 'rating' AS INTEGER) > $1",
        [
        'parameters' => [
            'p1' => 2
        ],
        'types' => [
            'p1' => Database::TYPE_INT64
        ]
    ]);

    foreach ($results as $row) {
        printf('VenueId: %s, VenueDetails: %s' . PHP_EOL, $row['venueid'], $row['venuedetails']);
    }
}

Python

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

def query_data_with_jsonb_parameter(instance_id, database_id):
    """Queries sample data using SQL with a JSONB parameter."""
    # instance_id = "your-spanner-instance"
    # database_id = "your-spanner-db-id"

    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    param = {"p1": 2}
    param_type = {"p1": param_types.INT64}

    with database.snapshot() as snapshot:
        results = snapshot.execute_sql(
            "SELECT venueid, venuedetails FROM Venues"
            + " WHERE CAST(venuedetails ->> 'rating' AS INTEGER) > $1",
            params=param,
            param_types=param_type,
        )

        for row in results:
            print("VenueId: {}, VenueDetails: {}".format(*row))

Ruby

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken.

Richten Sie zur Authentifizierung bei Cloud Spanner die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für eine lokale Entwicklungsumgebung einrichten. 

require "google/cloud/spanner"

def spanner_postgresql_jsonb_query_parameter project_id:, instance_id:, database_id:
  # project_id  = "Your Google Cloud project ID"
  # instance_id = "Your Spanner instance ID"
  # database_id = "Your Spanner database ID"
  spanner = Google::Cloud::Spanner.new project: project_id
  client  = spanner.client instance_id, database_id

  sql_query = <<~QUERY
    SELECT venueid, venuedetails
    FROM Venues
    WHERE CAST(venuedetails ->> 'rating' AS INTEGER) > $1
  QUERY

  # pass parameterized query's params represented by position
  results = client.execute sql_query, params: { p1: 5 }
  puts results.rows.first

  # Read JSONB value from table
  results = client.read "Venues", [:VenueId, :VenueDetails], keys: 19
  puts results.rows.first
end

Nicht unterstützte PostgreSQL-JSONB-Funktionen

Die folgenden Open-Source-PostgreSQL-JSONB-Features werden in Spanner JSONB nicht unterstützt:

  • PrimaryKey und ForeignKey
  • Indexierung, einschließlich des GIN-Index. Sie können stattdessen einen Spanner-Suchindex verwenden, der dieselben JSONB-Vorgänge wie ein GIN-Index beschleunigt. Weitere Informationen finden Sie unter JSON-Daten indexieren.
  • Ändern einer JSONB-Spalte in einen anderen Datentyp oder umgekehrt
  • Parametrisierte Abfragen mit nicht typisierten JSONB-Parametern in Tools verwenden, die das PostgreSQL-Wire-Protokoll verwenden

  • Verwendung der impliziten Umwandlung in der Abfrage-Engine. Im Gegensatz zu Open-Source-PostgreSQL wird die implizite Umwandlung von JSONB in Text nicht unterstützt. Sie müssen eine explizite Umwandlung vom Typ JSONB verwenden, um Funktionssignaturen abzugleichen. Beispiel:

        SELECT concat('abc'::text,  '{"key1":1}'::jsonb);  -- Returns error
    SELECT concat('abc'::text, CAST('{"key1":1}'::jsonb AS TEXT));  -- This works
    SELECT 3 + CAST('5'::jsonb AS INTEGER); -- This works