Insert JSON type into Google Cloud Spanner

spanner.md

Insert JSON type into Google Cloud Spanner

Suppose we have this schema:

CREATE TABLE Test (
  ID INT64 NOT NULL,
  Msg STRING(100),
  Data JSON
) PRIMARY KEY(id);

And to insert arbitrary row into this table using golang you need to do something like this:

func insert(ctx context.Context, c *spanner.Client) error {
	cb := func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		data := map[string]string{"foo": "bar"} // Your arbitrary JSON data.
		stmt := spanner.Statement{
			SQL: `INSERT INTO Test (ID, Msg, Data) VALUES (@id, @msg, @data)`,
			Params: map[string]interface{}{
				"id":   1,
				"msg":  "test JSON data type",
				"data": spanner.NullJSON{Value: data, Valid: true},
			},
		}
		_, err := txn.Update(ctx, stmt)
		if err != nil {
			return fmt.Errorf("failed to perform insert, err: %w", err)
		}
		return nil
	}
	_, err := c.ReadWriteTransaction(ctx, cb)
	if err != nil {
		return fmt.Errorf("failed to perform transaction, err: %w", err)
	}

	return nil
}

And that's it, as result you will have in your table next record:

id   msg                  data
1    test JSON data type  {"foo":"bar"}