Tables and JSON

Objects inside a table

JSON, short for JavaScript Object Notation, is a format for representing values. A JSON object contains named entries, such as "capacity_ml": 350. The name, capacity_ml, is called a key; 350 is its value. This use of “key” names an entry inside an object. It does not give a database record a primary key.

Values can be numbers, strings, booleans (true or false) or null. They can also be further objects or arrays, which are ordered lists of values. An object can therefore contain a small hierarchy: a product’s packaging can have its own height and width, while its care instructions form a list.

We’ll call the complete stored JSON value a document. Storing it in a JSON column keeps the hierarchy within the product row. The nested packaging object is not another row, and its height is not a column of the products table. PostgreSQL can still reach those values through JSON operators. The difference is how we address and constrain them. A document database may use documents as its main records; here we are choosing what belongs inside a JSON value in a relational row. Other databases have their own indexing and concurrency behaviour to assess.

The object also has a schema in the broader sense: writers and readers need an agreement about names, units, types and required entries. A reader expecting a number of millilitres cannot make sense of every value that valid JSON permits. Leaving those rules out of the table declaration moves responsibility to other checks and application code; it does not remove the need for them.

This fits the reasoning behind normalisation. We still ask which record a piece of information describes, and whether we have copied the same information into several places. Nesting a supplier’s current address inside every product it supplies would make an address correction touch many product objects. Keeping one supplier record avoids that repeated correction. Nesting a product’s own descriptive details need not create the same duplication.

Our shop’s Blue mug, P7, holds 350 millilitres. We could give that capacity a product column, put it inside a JSON object, or move it into a related details row. All three arrangements can record the same fact. The outlines below show where each would put it.

Typed columns

A column gives an attribute a name and a type across the table. With capacity_ml integer, the stored values are whole numbers and the chosen name records our convention that they represent millilitres. Queries can compare the values directly, and we can put a positive-value check beside them. We do not need each application to interpret a string such as “350 ml.”

Columns are often the straightforward choice when an attribute has a common meaning and an important role in the application. Product IDs and prices already have that role. If buyers regularly filter every product by weight, a consistently defined weight_g column may be useful too.

Our shop also sells a bowl, P8, whose descriptive measurement is a diameter of 18 centimetres. For this catalogue, we record mug capacity and bowl diameter as different attributes. Nullable columns let us represent that. A few optional attributes do not make a table defective. We still have to decide whether absence means “does not apply” or “not recorded,” and whether the distinction matters.

The pressure grows if every new product family introduces many different details. A tent has pole materials, a lamp has bulb fittings, and a book has an ISBN. One large table can accumulate fields that apply to only a small fraction of its records. The harder part is then expressing which combinations belong to which products. A positive capacity check does not say that every mug needs a capacity or that a book cannot have one.

We could add a product-kind field and conditional checks. That may be a good design for a small, stable set of kinds. If new kinds arrive frequently and their details are mostly displayed rather than used in shared calculations, another arrangement may make the catalogue easier to maintain.

Related records

We can keep the common product record and give mugs a related details record. A mug_details table has one row per mug, identified by the same product ID. The capacity field can be required there without forcing a bowl to have one.

CREATE TABLE mug_details (
  product_id text PRIMARY KEY
    REFERENCES products (product_id) ON DELETE CASCADE,
  capacity_ml integer NOT NULL CHECK (capacity_ml > 0)
);

INSERT INTO mug_details (product_id, capacity_ml)
VALUES ('P7', 350);

The primary key permits at most one mug-details row per product. The foreign key requires that the product exists. The type and check require a stored capacity from 1 to 2,147,483,647 millilitres, the positive range of PostgreSQL’s integer. They do not decide how to treat incoming fractions: a numeric-to-integer conversion can round before the check sees the value. We’ll give the input its own rule below. The deletion cascade removes the details if we intentionally delete the product.

Those declarations do not prove that every mug has a details row, or that a product cannot have both mug and bowl details. If we need exclusive categories or required details for each category, we must represent and enforce those rules too. Moving an attribute to a related table does not supply them automatically.

The extra table gives this kind of information its own shape. A catalogue page can join the product and its details by product ID. A report comparing mug capacities can read a consistently typed column in mug_details, without interpreting all the other product kinds.

Related rows are also useful when an attribute is really a collection. A product may have several supplier offers, each with a supplier reference, a price, and a lead time. Those offers can have their own identities and be changed independently. An array embedded in the product can display the same information, but its shape alone does not establish that a supplier ID names a real supplier.

Another possible table stores generic triples such as product ID, attribute name, and attribute value. That makes new attribute names easy to add as rows. It also moves typing and validation into a further design: “capacity” and “material” no longer have dedicated typed columns. Finding products with several attributes may require several matches to that table. Related records are useful because of the meaning and operations they preserve, not merely because they spread information across more rows.

A JSON attributes column

To run the SQL yourself, start the shared practice setup. It explains opening PostgreSQL, loading the files and choosing a fresh database for this article.

The runnable examples start with the four shop tables from Constraints. You can download that practice setup for an empty database, then run the JSON statements here, or download this article’s SQL path. The related-record snippet above is an alternative representation; if you tried it, start again from the four-table setup before following this JSON example.

For this shop, many product details are descriptive. A catalogue page needs them together, and new product kinds introduce new fields. We’ll use an attributes object for these varying details. The runnable example starts with just capacity and material for the mug, and diameter and material for the bowl:

ALTER TABLE products
ADD COLUMN attributes jsonb NOT NULL DEFAULT '{}'
  CHECK (jsonb_typeof(attributes) = 'object');

UPDATE products
SET attributes = '{"capacity_ml":350,"material":"ceramic"}'
WHERE product_id = 'P7';

UPDATE products
SET attributes = '{"diameter_cm":18,"material":"ceramic"}'
WHERE product_id = 'P8';

PostgreSQL offers json and jsonb types. We use jsonb, which stores a parsed representation and supports containment queries and indexes. It does not preserve insignificant whitespace, object key order, or duplicate object keys. If exact original JSON text is the fact we need to retain, that is a different requirement.

The new column still belongs to a relational row. P7 remains a product with a primary key, a required name and price, and order lines protected by foreign keys. Only the attributes’ internal shape varies. Storing an object has not required us to move the purchase records into documents or abandon their constraints.

NOT NULL prevents the whole attribute value from being absent, and the jsonb_typeof check requires an object rather than an array, string, or JSON null. The empty object {} is permitted. The default gives newly inserted products an empty object when the caller omits that column.

The object check does not require a capacity or establish its type. It accepts {"capacity_ml":"large"} as readily as {"capacity_ml":350}. The JSON parser establishes that the document is valid JSON; our check establishes that its outer value is an object. Neither establishes that its contents make sense for a mug.

That distinction matters as soon as we query the objects together. A column has one declared type across its rows; a JSON key can be absent in one object, contain a number in another, and contain a string in a third. Before comparing capacities, we need to know which representations our writers have used.

Missing keys, nulls, and types

An object can omit capacity_ml entirely, or include it with the JSON value null. Those are different documents. An omitted key may mean “not supplied”; an explicit null may mean “supplied without a known value.” JSON does not choose those business meanings for us, but it gives us two distinguishable representations.

SQL NULL is another possibility: the row’s entire attributes value can be missing. Our products definition forbids that, but we can compare it with the other cases in a separate query. PostgreSQL’s ? operator asks whether a top-level key exists. -> extracts a JSON value; ->> extracts its text representation.

The query builds five temporary sample rows rather than changing our products. WITH samples (label, attributes) names that set and its two columns; each parenthesized VALUES pair supplies a label and an attributes value. The ::jsonb notation asks PostgreSQL to interpret the preceding value as JSON. The final SELECT reads these sample rows and names its calculated outputs with AS.

WITH samples (label, attributes) AS (
  VALUES
    ('missing key', '{}'::jsonb),
    ('JSON null', '{"capacity_ml":null}'::jsonb),
    ('number', '{"capacity_ml":350}'::jsonb),
    ('string', '{"capacity_ml":"350"}'::jsonb),
    ('SQL NULL', NULL::jsonb)
)
SELECT label,
       attributes ? 'capacity_ml' AS has_key,
       jsonb_typeof(attributes -> 'capacity_ml') AS json_type,
       attributes ->> 'capacity_ml' AS text_value
FROM samples;

The text extractor turns both a missing key and JSON null into SQL NULL. It also gives the same text, '350', for the number 350 and the string “350.” Once we extract only that text, we have discarded distinctions present in the document. We can use key existence and jsonb_typeof when those distinctions matter.

This affects updates as well as reading. If one application omits an unknown capacity and another writes JSON null, a report checking only key existence will count them differently. If one writes a number and another writes a numeric string, a text comparison may make them look alike while another JSON query separates them. A shared convention is part of the schema even when no CREATE TABLE declaration names the field.

Queries and rules inside JSON

Suppose a customer asks for ceramic products with capacity of at least 300 ml. The @> operator checks whether the object contains the requested material entry. For the capacity comparison we extract text, then convert it to an integer.

SELECT product_id, name,
       (attributes ->> 'capacity_ml')::integer AS capacity_ml
FROM products
WHERE attributes @> '{"material":"ceramic"}'::jsonb
  AND (attributes ->> 'capacity_ml')::integer >= 300;

With our initial records, this returns P7, Blue mug, capacity 350. P8 has no capacity key. Its extraction produces SQL NULL, so the capacity comparison is unknown and the WHERE clause does not select it.

The cast relies on a convention about the values. A JSON string “350” also casts to the integer 350; a string “large” fails the conversion and can fail the query. Casting while reading is not the same as enforcing a JSON numeric type while writing.

We can add a check for the internal field. This one allows the key to be absent, but requires a JSON number whenever it is present:

ALTER TABLE products
ADD CONSTRAINT capacity_is_number
CHECK (
  NOT (attributes ? 'capacity_ml')
  OR jsonb_typeof(attributes -> 'capacity_ml') = 'number'
);

A missing key passes because the first part is true. JSON null, a string, or an object under that key fails because its JSON type is not 'number'. A number passes. This rule still permits zero, negative values, and fractions; a number-type check is not a positive-whole-millilitres rule. A permitted fraction such as 350.5 extracts as text that the query’s integer cast rejects, so this check alone does not guarantee that every stored capacity can be queried that way.

We could continue adding validation for those properties and for which product kinds need capacity. PostgreSQL can enforce many rules on JSON expressions. The cost is that the internal schema now lives in those expressions as well as in the writers and readers. If capacity becomes a central, consistently required application fact, a typed column or a dedicated details table may express it more clearly.

Finish the capacity rule

For this shop, capacity now matters to a common filter, and every mug-details row must supply it. We’ll keep capacity in mug_details and leave varying descriptions in JSON. The earlier number check was a useful intermediate step, but it did not finish that decision.

Our input policy accepts a JSON number only when its value is a whole number from 1 to 2,147,483,647. We reject a numeric string rather than guessing whether to interpret it, and reject a fraction rather than rounding it. The following conversion applies that policy to P7’s existing object. It creates the details row, then removes the old JSON entry in the same transaction, so capacity has one authoritative home. BEGIN starts that group of changes; COMMIT keeps them together. The INSERT … SELECT takes the product ID and converted capacity from its query into the two named destination columns. The JSON - operator then removes the named key from the object.

CASE WHEN … THEN … ELSE … END chooses which expression to evaluate. The outer case checks the JSON type before entering the numeric conversion. The inner case checks the range and compares the number with trunc, which removes any fractional part. Only a whole number in range reaches the integer cast. Rejected inputs produce SQL NULL, so the target’s NOT NULL rule refuses the insert. Putting a type test next to a cast with AND would not give us this evaluation boundary.

BEGIN;
CREATE TABLE mug_details (
  product_id text PRIMARY KEY
    REFERENCES products (product_id) ON DELETE CASCADE,
  capacity_ml integer NOT NULL CHECK (capacity_ml > 0)
);

INSERT INTO mug_details (product_id, capacity_ml)
SELECT product_id,
       CASE WHEN jsonb_typeof(attributes -> 'capacity_ml') = 'number'
         THEN CASE
           WHEN (attributes ->> 'capacity_ml')::numeric
                  BETWEEN 1 AND 2147483647
            AND (attributes ->> 'capacity_ml')::numeric
                  = trunc((attributes ->> 'capacity_ml')::numeric)
           THEN (attributes ->> 'capacity_ml')::numeric::integer
           ELSE NULL
         END
         ELSE NULL
       END
FROM products
WHERE product_id = 'P7';

UPDATE products
SET attributes = attributes - 'capacity_ml'
WHERE product_id = 'P7';
COMMIT;

If conversion is rejected, the transaction fails; issue ROLLBACK before correcting the input and trying again. Its JSON capacity has not been removed. For our valid 350, commit leaves that value in the details row and only material in P7’s object. The bowl needs no mug-details row. Required capacity for an existing mug-details row is still different from proving that every mug has such a row; we have not added a product-kind rule.

Future requests should use the same input policy before binding an integer value. The stored column and its positive check protect the resulting record; they cannot detect a fraction that a caller already rounded. This example migrates our known mug P7. A larger migration must first identify which products are mugs and handle rejected records explicitly.

SELECT p.product_id, p.name, m.capacity_ml
FROM products AS p
JOIN mug_details AS m ON m.product_id = p.product_id
WHERE p.attributes @> '{"material":"ceramic"}'::jsonb
  AND m.capacity_ml >= 300;

This returns P7, Blue mug, 350. The join finds the mug-details row by product ID; the material match still reads JSON, while the capacity comparison uses an integer directly. Every stored capacity is now usable by this query and its ordinary capacity index. Queries and joins gives more practice with the join and filter.

References deserve particular care. Putting supplier_id inside an attribute object does not create a foreign key to a supplier table. If supplier identity must be shared and enforced, an ordinary reference column or related offer row is a natural place for it. JSON can carry descriptive supplier notes alongside that reference.

Reading and changing a product

An embedded object is convenient when a catalogue page reads one product and needs most of its descriptive details together. The page receives one bundle without reconstructing those details from many attribute records. That convenience is about the access pattern; it does not mean every query over the catalogue becomes cheaper.

Filtering many products by a field inside the object needs a way to find those values. PostgreSQL can index JSON. A GIN index keeps entries for searchable keys and values, with links to rows that contain them. The ceramic-material match can use those entries to find candidate products, then check their objects, instead of examining every product’s attributes. It does not arrange capacities in numeric order. Our final design can use an ordinary B-tree index on mug_details.capacity_ml for that ordering, letting a range filter narrow its search to capacities of at least 300.

-- One option for containment queries over descriptions.
CREATE INDEX products_attributes_gin
ON products USING gin (attributes);

-- One option for ranges over the typed capacity column.
CREATE INDEX mug_details_capacity_idx
ON mug_details (capacity_ml);

These are different routes for different questions, not a requirement to create both. Neither declaration needs to reinterpret capacity text while building or maintaining the index. An expression index could instead index a value derived from JSON, but its conversion and validation would need to agree for every permitted input. As with an ordinary index, we must check actual queries and plans and account for the extra storage and write work. Native JSON support is a capability, not evidence of a performance result.

The way we group reads also affects writes. In PostgreSQL, changing one field in a jsonb object updates the containing row and takes a row lock. If one request changes a finish entry and another changes the descriptive material, they still write the same product row. Giving them different JSON keys does not give them independent row locks.

A row lock coordinates the writes, but cannot repair an object the application assembled from an old read. This separate illustration starts with two descriptive entries and follows what each editor sends:

Instead of sending B’s old complete object, we can set material in the current stored object with an expression:

UPDATE products
SET attributes = jsonb_set(
  attributes, '{material}', '"stoneware"'::jsonb
)
WHERE product_id = 'P7';

The first argument is the row’s current attributes value. The second, '{material}', names a path containing one key. The third, '"stoneware"'::jsonb, supplies the new JSON string, including its JSON quotation marks. This changes material to stoneware while retaining the other entries, including A’s matte finish in the illustration. It avoids sending back a complete object assembled from an earlier read, which might overwrite another writer’s intervening change. It still updates the product row; it does not make the key a separately stored record.

Separate rows are useful when pieces need separate ownership or frequent independent changes. Supplier offers, stock at each warehouse, and customer reviews need not compete with every change to the product description. An array of offers inside one product object may be pleasant to retrieve, while making every offer editor update the same row.

The right boundary is therefore partly a write decision. Information commonly read together can still deserve separate records if different operations change it independently, if other records refer to it, or if it needs rules over a collection of values.

Changing the schema

JSON lets us add a new key without first adding a column. It does not make readers understand that key. A catalogue can contain older objects without it and newer objects with it, while several application versions are running. We need to choose what each reader does with absence and which writers start supplying the field.

Renaming finish to surface_finish is still a migration. Readers that only know the old name will miss values written under the new name. We might temporarily read both, migrate stored objects, and stop writing the old key after the relevant application versions have changed. Keeping both indefinitely would leave another duplicated fact to reconcile.

Our capacity conversion was one such migration: before promoting a JSON attribute into a typed column, we must inspect the existing objects. Missing keys, JSON nulls, strings, and fractions need explicit decisions. Extracting them into a column may reveal inconsistencies that permissive writers had allowed to accumulate.

During a transition, two copies of capacity create the same update problem we met in schema design. We need one authoritative value and a controlled way to populate the other, then a clear point at which readers and writers use the new representation. The flexibility saved a structural change when we introduced the attribute; it did not remove the responsibility for changing its meaning later.

A design for this shop

For our current catalogue, we keep product identity, name, current price, and optional SKU as columns. They have stable meanings, common queries, and useful direct constraints. Orders and order lines remain separate records with their own keys and references. Agreed purchase prices remain on the lines, so changing a product’s attributes or current price cannot redefine what Ada agreed to pay.

We use an attributes jsonb object for the varying descriptive bundle on each product. The database requires an object; writers share conventions for names, units, and types inside it. Capacity has its own required integer field in mug_details, with an input policy that rejects fractions and numeric strings before conversion. It is no longer an independently editable JSON entry. Other frequently queried or correctness-critical attributes can earn stronger checks or move to typed fields. A collection with independently changing members can earn its own rows.

This mixture is often called a hybrid design. It remains understandable because each group of facts has a reason for being where it is. Purchase lines need separate identities and product references. The descriptive object belongs to one product. If we later add supplier offers that change independently, those can have their own rows without moving every descriptive detail out of JSON.

Other applications can arrive at different boundaries through the same questions. Start with what each piece describes and what identifies it. Follow an ordinary read and an ordinary change, then try an invalid value or a reference to a missing record. A design that is easy to display may be awkward to update or validate; those concrete operations show where to revise it.