Trabalhar com dados JSONB

Nesta página, descrevemos como trabalhar com o tipo de dados JSONB ao usar o Spanner.

JSONB é um tipo de dados do PostgreSQL usado para armazenar dados semiestruturados no dialeto PostgreSQL do Spanner. JSONB contém dados no formato JavaScript Object Notation (JSON), que segue a especificação descrita em RFC 7159 (em inglês).

Especificações

O tipo de dados JSONB do Spanner armazena uma representação normalizada do documento de entrada. Isso implica o seguinte:

• As aspas e os caracteres de espaço em branco não são preservados.
• Não é possível usar comentários. Transações ou consultas com comentários falham.
• As chaves de objeto são classificadas primeiro pelo comprimento da chave e depois lexicograficamente pelo comprimento equivalente da chave de objeto. Se houver chaves de objeto duplicadas, apenas a última será preservada.
• O tipo e o valor de tipos primitivos (string, boolean, number e null) serão preservados.
  • Os valores do tipo string são preservados exatamente.
  • Os zeros à direita são preservados. O formato de saída para valores do tipo number não usa notação científica.

• Os valores JSONB null são tratados como SQL não NULL. Por exemplo:

  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
  

• A ordem dos elementos da matriz JSONB é preservada.

Restrições

As seguintes restrições se aplicam ao Spanner JSONB:

• Os argumentos da função to_jsonb só podem ser dos tipos de dados do PostgreSQL compatíveis com o Spanner.
• Os valores do tipo número podem ter 4.932 dígitos antes do ponto decimal e 16.383 dígitos depois dele.
• O tamanho máximo permitido do formato de armazenamento normalizado é de 10 MB.
• Os documentos JSONB precisam ser codificados em UTF-8. Transações ou consultas com documentos JSONB codificados em outros formatos retornam um erro.

Criar uma tabela com colunas JSONB

É possível adicionar uma coluna JSONB a uma tabela ao criá-la.

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

Confira abaixo um exemplo de objeto JSONB VenueFeatures:

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

Adicionar e remover colunas JSONB de tabelas atuais

É possível adicionar e remover uma coluna JSONB usando instruções ALTER da seguinte maneira:

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

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

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

Observação:os exemplos de código da interface da biblioteca de cliente antiga para Java estão arquivados no GitHub (em inglês).

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();

Observação:os exemplos de código da interface da biblioteca de cliente antiga para Node.js estão arquivados no GitHub.

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

Observação:os exemplos de código da interface da biblioteca de cliente antiga para PHP estão arquivados no GitHub.

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

Observação:os exemplos de código da interface da biblioteca de cliente antiga para Python estão arquivados no GitHub.

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

Modificar dados JSONB

É possível modificar uma coluna JSONB como qualquer outra.

Confira um exemplo:

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

O exemplo a seguir mostra como atualizar dados JSONB usando as bibliotecas de cliente do Spanner.

C++

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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#

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

/**
 * 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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Indexar dados JSON

É possível acelerar a consulta de dados JSONB usando índices secundários e índices de pesquisa com seus dados JSONB. O Spanner não é compatível com o uso de colunas do tipo JSONB como chaves em índices secundários.

Usar índice secundário

Os índices secundários são úteis ao filtrar valores escalares em um documento JSONB. Para usar índices secundários com JSONB, crie uma coluna gerada que extraia os dados escalares relevantes e os converta em um tipo de dados SQL adequado. Em seguida, você pode criar um índice secundário nessa coluna gerada. O índice acelera as consultas qualificadas executadas na coluna gerada.

No exemplo a seguir, você cria um índice VenuesByCapacity que o banco de dados usa para encontrar os locais com capacidade maior que 1.000. Em vez de verificar todas as linhas, o Spanner usa o índice para localizar as linhas relevantes, o que melhora o desempenho da consulta, especialmente para tabelas grandes.

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;

Usar índices de pesquisa

Observação:esse recurso está disponível nas edições Enterprise e Enterprise Plus do Spanner. Para mais informações, consulte a visão geral das edições do Spanner.

Os índices de pesquisa são úteis quando você consulta documentos JSONB dinâmicos ou variados. Ao contrário dos índices secundários, é possível criar índices de pesquisa em qualquer documento JSONB armazenado em uma coluna JSONB. O índice de pesquisa se adapta automaticamente a variações em documentos JSON, entre diferentes linhas e ao longo do tempo.

No exemplo a seguir, você cria um índice de pesquisa VenuesByVenueDetails que o banco de dados usa para encontrar os locais com detalhes específicos, como tamanho e horário de funcionamento. Em vez de verificar todas as linhas, o Spanner usa o índice para localizar as linhas relevantes, o que melhora o desempenho da consulta, especialmente em tabelas grandes.

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;

Para mais informações, consulte Índices de pesquisa JSON.

Consultar dados JSONB

É possível consultar colunas JSONB com base nos valores dos campos subjacentes. O exemplo a seguir extrai VenueId e VenueName de Venues em que VenueFeatures tem um valor rating maior que 3.5.

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

O exemplo a seguir mostra como consultar dados JSONB usando as bibliotecas de cliente do Spanner.

C++

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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, absl::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#

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

/**
 * 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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Para saber como instalar e usar a biblioteca de cliente do Spanner, consulte Bibliotecas de cliente do Spanner.

Para autenticar no Spanner, configure o Application Default Credentials. Para mais informações, consulte Configurar a autenticação para um ambiente de desenvolvimento local.

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

Recursos JSONB do PostgreSQL sem suporte

Os seguintes recursos do PostgreSQL de código aberto JSONB não são compatíveis com o Spanner JSONB:

• Ordenação, comparação e agregação
• PrimaryKey e ForeignKey
• Indexação, incluindo o índice GIN. Em vez disso, use um índice de pesquisa do Spanner, que acelera as mesmas operações JSONB que um índice GIN. Para mais informações, consulte Indexar dados JSON.
• Alterar uma coluna JSONB para ou de qualquer outro tipo de dados
• Como usar consultas parametrizadas com parâmetros JSONB sem tipo em ferramentas que usam o protocolo de rede do PostgreSQL

• Usar coerção no mecanismo de consulta. Ao contrário do PostgreSQL de código aberto, a coerção implícita de JSONB para texto não é compatível. É necessário usar a conversão explícita do tipo JSONB para corresponder às assinaturas de função. Exemplo:

      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