Skip to content

Instantly share code, notes, and snippets.

@cn0047
Created December 22, 2021 21:53
Show Gist options
  • Save cn0047/50cf0d06b6cb00f95535e5cfb1aef21b to your computer and use it in GitHub Desktop.
Save cn0047/50cf0d06b6cb00f95535e5cfb1aef21b to your computer and use it in GitHub Desktop.
Insert JSON type into Google Cloud Spanner

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"}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment