Deviopath › PostgreSQL

Customer JSON Documents

Hard · JSON

jsonb_build_object('key', value, ...) builds a JSON object, and jsonb_agg(expr ORDER BY ...) collects values from many rows into a JSON array. jsonb stores keys in its own order, so the order you write the keys in does not matter.

Return one row per customer with customer_id and doc, a jsonb object with these keys:

- name and city - order_count: the number of orders - orders: an array of {"id", "amount", "status"} objects, sorted by ordered_at, then id

Customers with no orders get "order_count": 0 and "orders": [].

Next: Items Bought on iOS