The shop's orders are in two models:
- Relational:
orders,order_items,productsandcustomers, linked by keys. - Documents:
order_docs, one JSON document per order, with the customer and the items embedded.
- From the relational tables, list the product
nameand thequantityof every item in order 101. - From
order_docs, list theproductand theqtyof every item in order 107, as text. - In how many documents of
order_docsdoes the nameAva Chenappear? - Ava Chen changes her name to Ava Li. Change it in both models: in
customersand in every document.
To change one value inside a document, use jsonb_set(document, path, new_value). It returns the document with the value at path replaced. The path is a list of keys, the new value is JSON. For example, to change the status of order 115:
sql
UPDATE order_docs
SET doc = jsonb_set(doc, '{status}', '"shipped"')
WHERE id = 115;- Which model stores Ava's name only once? Answer relational or document.