1. SQL Reference Guide
  2. Data Row Management
  3. JSON Collection Table Example

JSON Collection Table Example

This section describes a sample JSON collection table created for a shopping application.

Table DDL:
CREATE TABLE IF NOT EXISTS storeAcct (contactPhone STRING, PRIMARY KEY(SHARD(contactPhone)))
      AS JSON COLLECTION

This table is a collection of documents with the shopper's contactPhone as the primary key. The rows represent individual shopper's records. The individual rows need not include the same fields in the document. The shopper's preferences such as name, address, email, notify, and so forth are stored as top-level fields in the document. The documents can include any number of JSON fields such as wishlist, cart, and orders that contain shopping-related information.

The JSON array wishlist contains the items wishlisted by the shoppers. Each element of this array includes nested JSON fields such as the item and priceperunit to store the product name and price details of the wishlisted item.

The JSON array cart contains the products that the shopper intends to purchase. Each element of this array includes nested JSON fields such as item, quantity, and priceperunit to store the product name, number of units, and price of each unit.

The JSON array orders contains the products that the shopper has purchased. Each element of this array includes nested JSON fields such as the orderID, item, priceperunit, EstDelivery, and status to store the order number, product name, price of each unit, estimated date of delivery for the product, and status of the order.

The following code inserts data into the shopping application table. You can use this data to follow along with the examples explained in the topics.
insert into  storeAcct(contactPhone, firstName, lastName, address, cart) values("1817113382", "Adam", "Smith", {"street" : "Tex Ave", "number" : 401, "city" : "Houston", "state" : "TX",   "zip" : 95085}, [{"item" : "handbag", "quantity" : 1, "priceperunit" : 350},{"item" : "Lego", "quantity" : 1, "priceperunit" : 5500}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, gender, address, notify, cart, wishlist) values("1917113999", "Sharon", "Willard", "F", {"street" : "Maine", "number" : 501, "city" : "San Jose", "state" : "San Francisco",   "zip" : 95095},"yes", [{"item" : "wallet", "quantity" : 2, "priceperunit" : 950},{"item" : "wall art", "quantity" : 1, "priceperunit" : 9500}], [{"item" : "Tshirt", "priceperunit" : 500},{"item" : "Jenga", "priceperunit" : 850}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, address, notify, cart, orders) values("1617114988", "Lorenzo", "Phil", {"Dropbox" : "Presidency College", "city" : "Kansas City", "state" : "Alabama",   "zip" : 95065},"yes", [{"item" : "A4 sheets", "quantity" : 2, "priceperunit" : 500},{"item" : "Mobile Holder", "quantity" : 1, "priceperunit" : 700}], [{"orderID" : "101200", "item" : "AG Novels 1", "EstDelivery" : "2023-11-15", "priceperunit" : 950, "status" : "Preparing to dispatch"},{"orderID" : "101200", "item" : "Wallpaper", "EstDelivery" : "2023-11-01", "priceperunit" : 950, "status" : "Transit"}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, address, cart, orders) values("1517113582", "Dierdre", "Amador", {"street" : "Tex Ave", "number" : 651, "city" : "Houston", "state" : "TX",   "zip" : 95085}, NULL, [{"orderID" : "201200", "item" : "handbag", "EstDelivery" : "2023-11-01", "priceperunit" : 350},{"orderID" : "201201", "item" : "Lego", "EstDelivery" : "2023-11-01", "priceperunit" : 5500}]) RETURNING *;

insert into  storeAcct(contactPhone, firstName, lastName, address, notify, cart, orders) values("1417114488", "Doris", "Martin", {"Dropbox" : "Presidency College", "city" : "Kansas City", "state" : "Alabama",   "zip" : 95065},"yes", [{"item" : "Notebooks", "quantity" : 2, "priceperunit" : 50},{"item" : "Pens", "quantity" : 2, "priceperunit" : 50}], [{"orderID" : "301200", "item" : "Laptop Bag", "EstDelivery" : "2023-11-15", "priceperunit" : 1950, "status" : "Preparing to dispatch"},{"orderID" : "301200", "item" : "Mouse", "EstDelivery" : "2023-11-02", "priceperunit" : 950, "status" : "Transit"}]) RETURNING *;