PostgreSQL: how do I store and query JSON data efficiently?

Question

I have a `products` table where each category has different attributes (phones have `ram`, shoes have `size`). Should I use a JSONB column? How do I query it fast?

Accepted Answer

JSONB is a good fit for variable attributes. Keep the **common, queried-everywhere fields as real columns** (name, price, category_id) and put the variable part in JSONB:

```sql
CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  price_cents int NOT NULL,
  attrs jsonb NOT NULL DEFAULT '{}'
);

-- containment query, uses a GIN index
CREATE INDEX products_attrs_gin ON products USING gin (attrs jsonb_path_ops);
SELECT * FROM products WHERE attrs @> '{"ram": "8GB"}';

-- a frequently filtered key: expression index
CREATE INDEX products_size_idx ON products ((attrs->>'size'));
SELECT * FROM products WHERE attrs->>'size' = '42';
```

Use `@>` (containment) to benefit from the GIN index. `->>` returns text, `->` returns JSON. Validate the JSON shape in your application (e.g. with zod) since the database won't enforce it.

1 réponse