Is there a (key:value) pair datatype that can be used as a column in SQL – Specifically Google Cloud Spanner?

Given the scenario that I would like to store sales of a store in a table in a database in Google Cloud Spanner. That table would (in addition to the sale ID as the primary key) have to store a list of product IDs sold, along with the quantity of each product sold. Ideally, this would be in the form of a column such as

productsSold ARRAY<product OBJECT <productID INT64, quantity INT64>>

I.e. each element in the array would be an object containing the product ID along with the quantity of the corresponding product sold. I saw the STRUCT datatype in Google Cloud Spanner, which would be ideal, but it seems you can’t create columns using that.

Alternatively, it could just be stored in two separate arrays as

productIDs ARRAY
quantities ARRAY

But this has the possibility of the arrays being out of sync as each element in productIDs isn’t directly linked to a corresponding element in quantities.

What would you recommend is the ideal way to store this data according to the best database conventions and through what is possible with Google Cloud Spanner? Thanks in advance!

Answer

First, I would normally design this to use two interleaved tables, so something like this:

CREATE TABLE Products (
  ProductId STRING(36) NOT NULL,
  Name STRING(200),
) PRIMARY KEY (ProductId);

CREATE TABLE Sales (
  SaleId STRING(36) NOT NULL,
  CustomerId STRING(36) NOT NULL,
) PRIMARY KEY (SaleId);

CREATE ProductSales (
  SaleId STRING(36) NOT NULL,
  ProductSaleId INT64 NOT NULL,
  ProductId STRING(36) NOT NULL,
  Quantity INT64,
) PRIMARY KEY (SaleId, ProductSaleId), INTERLEAVE IN PARENT (Sales);

If you for some reason must/want to store them in a single table using arrays, then you could use a CHECK CONSTRAINT to ensure the arrays have the same size:

CREATE TABLE Sales (
  SaleId STRING(36) NOT NULL,
  ProductIds ARRAY<STRING(36)>,
  Quantities ARRAY<INT64>,
  CONSTRAINT arrays_equal_length CHECK(ARRAY_LENGTH(ProductIds) = ARRAY_LENGTH(Quantities)),
) PRIMARY KEY ProductId;