NoSQL: documents, key-value, and when they beat SQL

// orders.mongo.js — run: mongosh shop --quiet orders.mongo.js
const show = (x) => print(JSON.stringify(x));
db.orders.drop();

db.orders.insertMany([
  {
    _id: 1001,
    status: "paid",
    customer: { id: 7, name: "Aigerim", email: "aigerim@example.com" },
    items: [
      { sku: "LATTE", title: "Latte", price: 1600, qty: 2 },
      { sku: "CROISSANT", title: "Croissant", price: 1200, qty: 1 }
    ]
  },
  {
    _id: 1002,
    status: "new",
    customer: { id: 8, name: "Timur", email: "timur@example.com" },
    items: [{ sku: "LATTE", title: "Latte", price: 1600, qty: 1 }]
  },
  {
    _id: 1003,
    status: "paid",
    customer: { id: 7, name: "Aigerim", email: "aigerim@example.com" },
    items: [{ sku: "ESPRESSO", title: "Espresso", price: 1100, qty: 3 }]
  }
]);

// 1. The whole order in one read, no JOIN
show(db.orders.findOne({ _id: 1001 }, { _id: 0, "customer.name": 1, "items.sku": 1, "items.qty": 1 }));

// 2. Orders that contain a latte (search inside an array)
show(db.orders.find({ "items.sku": "LATTE" }, { _id: 1 }).toArray());

// 3. Revenue per product in paid orders
show(db.orders.aggregate([
  { $match: { status: "paid" } },
  { $unwind: "$items" },
  { $group: { _id: "$items.sku", revenue: { $sum: { $multiply: ["$items.price", "$items.qty"] } } } },
  { $sort: { revenue: -1 } }
]).toArray());

// 4. Add an item and change the status: one atomic write to one document
show(db.orders.updateOne(
  { _id: 1002 },
  { $push: { items: { sku: "CROISSANT", title: "Croissant", price: 1200, qty: 1 } }, $set: { status: "paid" } }
));

// 5. Customer data is copied into every order: the email changed, so fix every copy
show(db.orders.updateMany({ "customer.id": 7 }, { $set: { "customer.email": "aigerim.new@example.com" } }));

$ docker cp orders.mongo.js kb-mongo-nosql:/tmp/
$ docker exec kb-mongo-nosql mongosh shop --quiet /tmp/orders.mongo.js
{"customer":{"name":"Aigerim"},"items":[{"sku":"LATTE","qty":2},{"sku":"CROISSANT","qty":1}]}
[{"_id":1001},{"_id":1002}]
[{"_id":"ESPRESSO","revenue":3300},{"_id":"LATTE","revenue":3200},{"_id":"CROISSANT","revenue":1200}]
{"acknowledged":true,"insertedId":null,"matchedCount":1,"modifiedCount":1,"upsertedCount":0}
{"acknowledged":true,"insertedId":null,"matchedCount":2,"modifiedCount":2,"upsertedCount":0}

// schema.mongo.js — run after orders.mongo.js
const show = (x) => print(JSON.stringify(x));

// Nobody checks the schema: a price stored as a string is accepted
show(db.orders.insertOne({ _id: 1004, status: "paid", items: [{ sku: "LATTE", price: "1600", qty: 1 }] }));

// ...and breaks the report later, at read time
try {
  db.orders.aggregate([
    { $match: { status: "paid" } },
    { $unwind: "$items" },
    { $group: { _id: "$items.sku", revenue: { $sum: { $multiply: ["$items.price", "$items.qty"] } } } }
  ]).toArray();
} catch (e) {
  print(e.message);
}
db.orders.deleteOne({ _id: 1004 });

// A $jsonSchema validator: you switch the checks on yourself
db.runCommand({
  collMod: "orders",
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["status", "items"],
      properties: {
        status: { enum: ["new", "paid", "cancelled"] },
        items: {
          bsonType: "array",
          minItems: 1,
          items: {
            bsonType: "object",
            required: ["sku", "price", "qty"],
            properties: {
              price: { bsonType: ["int", "long", "double", "decimal"], minimum: 0 },
              qty: { bsonType: "int", minimum: 1 }
            }
          }
        }
      }
    }
  }
});

try {
  db.orders.insertOne({ _id: 1005, status: "paid", items: [{ sku: "LATTE", price: "1600", qty: 1 }] });
} catch (e) {
  print(e.errmsg);
}

$ docker exec kb-mongo-nosql mongosh shop --quiet /tmp/schema.mongo.js
{"acknowledged":true,"insertedId":1004}
Executor error during aggregate command on namespace: shop.orders :: caused by :: $multiply only supports numeric types, not string
Document failed validation

$ docker exec -it kb-redis-nosql redis-cli
127.0.0.1:6379> SET session:9f2c '{"userId":7,"cart":["LATTE"]}' EX 1800
OK
127.0.0.1:6379> GET session:9f2c
"{\"userId\":7,\"cart\":[\"LATTE\"]}"
127.0.0.1:6379> TTL session:9f2c
(integer) 1800
127.0.0.1:6379> INCR views:product:LATTE
(integer) 1
127.0.0.1:6379> INCR views:product:LATTE
(integer) 2
127.0.0.1:6379> DEL session:9f2c
(integer) 1
127.0.0.1:6379> GET session:9f2c
(nil)

-- orders_jsonb.sql — run: psql -U postgres -f orders_jsonb.sql
DROP TABLE IF EXISTS orders_doc;

CREATE TABLE orders_doc (
  id     integer PRIMARY KEY,
  status text NOT NULL CHECK (status IN ('new', 'paid', 'cancelled')),
  doc    jsonb NOT NULL
);

INSERT INTO orders_doc VALUES
  (1001, 'paid', '{"customer": {"id": 7, "name": "Aigerim", "email": "aigerim@example.com"},
                   "items": [{"sku": "LATTE", "title": "Latte", "price": 1600, "qty": 2},
                             {"sku": "CROISSANT", "title": "Croissant", "price": 1200, "qty": 1}]}'),
  (1002, 'new',  '{"customer": {"id": 8, "name": "Timur", "email": "timur@example.com"},
                   "items": [{"sku": "LATTE", "title": "Latte", "price": 1600, "qty": 1}]}'),
  (1003, 'paid', '{"customer": {"id": 7, "name": "Aigerim", "email": "aigerim@example.com"},
                   "items": [{"sku": "ESPRESSO", "title": "Espresso", "price": 1100, "qty": 3}]}');

-- GIN index for the containment operator @>
CREATE INDEX orders_doc_gin ON orders_doc USING GIN (doc jsonb_path_ops);

-- 1. The whole order is one row, no JOIN
SELECT doc -> 'customer' ->> 'name' AS customer,
       jsonb_path_query_array(doc, '$.items[*].sku') AS skus
FROM orders_doc WHERE id = 1001;

-- 2. Orders that contain a latte
SELECT id FROM orders_doc
WHERE doc @> '{"items": [{"sku": "LATTE"}]}'
ORDER BY id;

-- 3. Revenue per product in paid orders
SELECT item ->> 'sku' AS sku,
       sum((item ->> 'price')::int * (item ->> 'qty')::int) AS revenue
FROM orders_doc, jsonb_array_elements(doc -> 'items') AS item
WHERE status = 'paid'
GROUP BY 1
ORDER BY revenue DESC;

-- 4. Add an item and change the status in one UPDATE
UPDATE orders_doc
SET doc = jsonb_set(doc, '{items}',
          (doc -> 'items') || '{"sku": "CROISSANT", "title": "Croissant", "price": 1200, "qty": 1}'),
    status = 'paid'
WHERE id = 1002;

-- 5. Customer data is copied into every order: fix every copy
UPDATE orders_doc
SET doc = jsonb_set(doc, '{customer,email}', '"aigerim.new@example.com"')
WHERE doc @> '{"customer": {"id": 7}}';

$ docker cp orders_jsonb.sql kb-pg-nosql:/tmp/
$ docker exec kb-pg-nosql psql -U postgres -f /tmp/orders_jsonb.sql
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE INDEX
 customer |          skus          
----------+------------------------
 Aigerim  | ["LATTE", "CROISSANT"]
(1 row)

  id  
------
 1001
 1002
(2 rows)

    sku    | revenue 
-----------+---------
 ESPRESSO  |    3300
 LATTE     |    3200
 CROISSANT |    1200
(3 rows)

UPDATE 1
UPDATE 2

-- json keeps the text as is, jsonb keeps the parsed value
SELECT '{"b": 1, "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;

-- With three rows the planner prefers a full scan; switch it off to see that the index fits
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id FROM orders_doc WHERE doc @> '{"items": [{"sku": "LATTE"}]}';
RESET enable_seqscan;

-- jsonb does not check the structure either: a string price gets in...
INSERT INTO orders_doc VALUES (1004, 'paid', '{"items": [{"sku": "LATTE", "price": "1600", "qty": 1}]}');
DELETE FROM orders_doc WHERE id = 1004;

-- ...until you add a constraint
ALTER TABLE orders_doc ADD CONSTRAINT items_is_array
  CHECK (jsonb_typeof(doc -> 'items') = 'array');
INSERT INTO orders_doc VALUES (1005, 'new', '{"items": {"sku": "LATTE"}}');

$ docker exec kb-pg-nosql psql -U postgres -f /tmp/jsonb_extra.sql
         as_json          |     as_jsonb     
--------------------------+------------------
 {"b": 1, "a": 2, "a": 3} | {"a": 3, "b": 1}
(1 row)

SET
                             QUERY PLAN                              
---------------------------------------------------------------------
 Bitmap Heap Scan on orders_doc
   Recheck Cond: (doc @> '{"items": [{"sku": "LATTE"}]}'::jsonb)
   ->  Bitmap Index Scan on orders_doc_gin
         Index Cond: (doc @> '{"items": [{"sku": "LATTE"}]}'::jsonb)
(4 rows)

RESET
INSERT 0 1
DELETE 1
ALTER TABLE
psql:/tmp/jsonb_extra.sql:18: ERROR:  new row for relation "orders_doc" violates check constraint "items_is_array"
DETAIL:  Failing row contains (1005, new, {"items": {"sku": "LATTE"}}).

-- orders_relational.sql — the same orders in tables
DROP TABLE IF EXISTS order_items, orders, products, customers;

CREATE TABLE customers (
  id    integer PRIMARY KEY,
  name  text NOT NULL,
  email text NOT NULL UNIQUE
);

CREATE TABLE products (
  sku   text PRIMARY KEY,
  title text NOT NULL
);

CREATE TABLE orders (
  id          integer PRIMARY KEY,
  customer_id integer NOT NULL REFERENCES customers (id),
  status      text NOT NULL CHECK (status IN ('new', 'paid', 'cancelled'))
);

CREATE TABLE order_items (
  order_id integer NOT NULL REFERENCES orders (id),
  sku      text    NOT NULL REFERENCES products (sku),
  price    integer NOT NULL CHECK (price >= 0),  -- price at the time of the order
  qty      integer NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, sku)
);

INSERT INTO customers VALUES (7, 'Aigerim', 'aigerim@example.com'), (8, 'Timur', 'timur@example.com');
INSERT INTO products VALUES ('LATTE', 'Latte'), ('CROISSANT', 'Croissant'), ('ESPRESSO', 'Espresso');
INSERT INTO orders VALUES (1001, 7, 'paid'), (1002, 8, 'new'), (1003, 7, 'paid');
INSERT INTO order_items VALUES
  (1001, 'LATTE', 1600, 2), (1001, 'CROISSANT', 1200, 1),
  (1002, 'LATTE', 1600, 1),
  (1003, 'ESPRESSO', 1100, 3);

-- Revenue per product: the same result, via JOIN
SELECT i.sku, sum(i.price * i.qty) AS revenue
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'paid'
GROUP BY i.sku
ORDER BY revenue DESC;

-- The email lives in one place: one row
UPDATE customers SET email = 'aigerim.new@example.com' WHERE id = 7;

-- The schema rejects a string price and an item of a missing order
INSERT INTO order_items VALUES (1002, 'ESPRESSO', 'one thousand', 1);
INSERT INTO order_items VALUES (9999, 'LATTE', 1600, 1);

$ docker exec kb-pg-nosql psql -U postgres -f /tmp/orders_relational.sql
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 3
INSERT 0 4
    sku    | revenue 
-----------+---------
 ESPRESSO  |    3300
 LATTE     |    3200
 CROISSANT |    1200
(3 rows)

UPDATE 1
psql:/tmp/orders_relational.sql:48: ERROR:  invalid input syntax for type integer: "one thousand"
LINE 1: INSERT INTO order_items VALUES (1002, 'ESPRESSO', 'one thous...
                                                          ^
psql:/tmp/orders_relational.sql:49: ERROR:  insert or update on table "order_items" violates foreign key constraint "order_items_order_id_fkey"
DETAIL:  Key (order_id)=(9999) is not present in table "orders".