Turning a Deeply Nested API Response Into Three Tables

An API returns an order, and inside the order is the customer, and inside the customer is an address, and next to them is a list of items. One JSON document, three or four entities. You can load it as it came, in one jsonb column, and query it with JSON operators for the rest of your life. Or you can take it apart once into tables that a database can index, constrain and join. This article does the second for a realistic order response, step by step, and reports what the data said about where each nested piece really belongs, which was not what the nesting suggested.

Look at the document and name the entities

One order, abbreviated:

{
  "id": 2,
  "customer": { "id": 2, "name": "Customer 2", "address": { "city": "Lima", "zip": "10002" } },
  "placed": "2026-03-03",
  "paid": false,
  "items": [ { "sku": "S3", "qty": 4, "price": 0.02 }, { "sku": "S4", "qty": 5, "price": 0.04 } ]
}

Three things in it have identities of their own: the order (id), the customer (customer.id) and the line item (no id of its own; it is identified by its order and its position). The address could be a fourth entity, but let the data decide that. Start with questions about the data, answered with counts:

SELECT count(*)                                                              AS orders,
       count(DISTINCT doc#>>'{customer,id}')                                 AS customer_ids,
       count(DISTINCT (doc#>>'{customer,id}', doc#>>'{customer,name}'))      AS id_name_pairs,
       count(DISTINCT (doc#>>'{customer,id}', doc#>>'{customer,address,city}')) AS id_city_pairs,
       count(DISTINCT (doc#>>'{customer,id}', doc#>>'{customer,address,zip}'))  AS id_zip_pairs
FROM raw_orders;

 orders | customer_ids | id_name_pairs | id_city_pairs | id_zip_pairs
--------+--------------+---------------+---------------+--------------
  20000 |         1000 |          1000 |          3000 |        20000

Read the columns as tests of a hypothesis. 20,000 orders belong to 1,000 customers, so each customer is repeated 20 times in the raw data. id_name_pairs equals customer_ids: every customer id has exactly one name, so name is a property of the customer. But id_city_pairs is 3,000 and id_zip_pairs is 20,000. The same customer id shows up with three different cities and, across orders, a different zip code on every order. The address is not a property of the customer. It is the address the order was sent to, a snapshot at the time of the order. Putting it in customers would force a choice of which one to keep and would throw away 19 of 20, or worse, would present the wrong one as current.

That is the kind of result that only a count reveals. The nesting in the document suggested customer.address. The data says order.ship_to. Build the tables from what the data says and name the column accordingly (ship_city, ship_zip).

Create the tables with their constraints

CREATE TABLE customers (
  id   int PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE orders (
  id          int PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id),
  placed      date NOT NULL,
  paid        boolean NOT NULL,
  ship_city   text,
  ship_zip    text
);
CREATE TABLE order_items (
  order_id int NOT NULL REFERENCES orders(id),
  pos      int NOT NULL,
  sku      text NOT NULL,
  qty      int NOT NULL CHECK (qty > 0),
  price    numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, pos)
);

Write the constraints first, before the data. They are the checks on the extraction, and the database enforces them for you. Types matter as well: placed is a date, paid a boolean, price a numeric(10,2) and not a float (money should not be a double). The conversion from JSON text happens in the inserts, and a value that does not fit stops the load with an error, which is what you want.

Load customers, then orders, then items

The order of the inserts follows the foreign keys: parents before children.

INSERT INTO customers
SELECT DISTINCT ON ((doc#>>'{customer,id}')::int)
       (doc#>>'{customer,id}')::int, doc#>>'{customer,name}'
FROM raw_orders
ORDER BY (doc#>>'{customer,id}')::int, (doc->>'id')::int;

DISTINCT ON keeps one row per customer id, and the ORDER BY decides which one: here the first order by id, so the choice is deterministic. When the nested copies of a customer disagree (here they do not, for the name), that choice is a decision you are making for the data. Consider keeping the latest instead (ORDER BY ..., placed DESC) and report the disagreement count first. It took 0.03 s and inserted 1,000 rows.

INSERT INTO orders
SELECT (doc->>'id')::int, (doc#>>'{customer,id}')::int, (doc->>'placed')::date,
       (doc->>'paid')::boolean, doc#>>'{customer,address,city}', doc#>>'{customer,address,zip}'
FROM raw_orders;

One row per document, 20,000 rows, 0.12 s. The customer_id column is the bridge that replaces the nested object.

INSERT INTO order_items
SELECT (o.doc->>'id')::int, i.n, i.item->>'sku', (i.item->>'qty')::int, (i.item->>'price')::numeric
FROM raw_orders o
CROSS JOIN LATERAL jsonb_array_elements(o.doc->'items') WITH ORDINALITY AS i(item, n);

50,000 rows in 0.33 s. WITH ORDINALITY supplies pos, which, with the order id, is the key of an item (flattening JSON in SQL with lateral joins explains the construct). The counts: customers 1000, orders 20000, order_items 50000.

Check the result against the source

The load ran without errors, which tells you the constraints held and not that nothing was lost. Compare with the raw documents:

SELECT sum(qty * price) FROM order_items;          -- 6375490.00
SELECT count(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id);   -- 0
SELECT count(*) FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN customers c ON c.id = o.customer_id;          -- 50000

The total equals the one computed from the raw JSON (6,375,490.00 in the other articles on this data set), no order is without items, and joining the three tables back together returns exactly the 50,000 item rows: none lost to a missing parent, none doubled by a duplicate customer. Those three statements are your acceptance test. Write them down with the load script, run them every time.

The database also proves the constraints work. Inserting an order for customer 424242 failed with insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey" and Key (customer_id)=(424242) is not present in table "customers". Inserting an item at an existing position failed with duplicate key value violates unique constraint "order_items_pkey" and Key (order_id, pos)=(1, 1) already exists. A dirty source cannot slip duplicates or orphans into these tables, and the next load will tell you the same.

What the tables buy you

A typical question is now a plain join:

SELECT c.name, count(DISTINCT o.id) AS orders, round(sum(i.qty*i.price), 2) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
GROUP BY 1 ORDER BY 3 DESC LIMIT 3;

Customer 995 came first, with 80 orders and 14,480.00 spent. The same question on the raw jsonb needs a lateral join and casts inside the aggregate, and no index can help it. With the tables, a B-tree index on orders.customer_id makes a lookup of one customer's orders a few page reads, and a change of a customer's name is one UPDATE of one row instead of 20 documents. The three tables also took 5,336 kB, against 8,168 kB for the raw documents, because the keys ("customer", "address" and so on) are no longer stored in every row and the customers are stored once.

Keep the raw documents, at least for a while. The raw_orders table (or the files in object storage) is the audit trail for the load, and the way to reload after a mistake in the mapping (storing raw API responses before you transform them). The transformation is cheap to re-run, so errors are cheap to fix.

When the next response is not the same

An API changes. A field is added, a nested object becomes optional, price turns up as a string. The load script above stops loudly on type errors, which is right, and ignores new keys, which is a silent drop. Count keys in the raw table against the keys you map (detecting when an API changes its JSON shape), and add a raw jsonb column to orders if you want the full original to travel with each row.

When three tables are one too many

The data is read once. For a one-off analysis, flatten into a single wide table or query the JSON with DuckDB. Constraints on a table you will drop tomorrow are ceremony (analyzing JSON data with SQL).

The items are never queried on their own. If you only ever show an order with its lines, a jsonb column of items on orders is simpler and loses nothing.

Another system owns the model. If the customer table already exists in your warehouse, map customer.id to it and do not load customers from orders at all.

The statements used are documented in the PostgreSQL manual: INSERT, SELECT with DISTINCT ON and constraints. To read a sample document, use the JSON formatter.