Storing JSON in PostgreSQL and MySQL: JSONB, Indexes, and Query Syntax

Quick answer: In PostgreSQL, use the jsonb type (not json), read fields with ->>, filter with @>, and add a GIN index for containment queries or an expression index for a single hot field. In MySQL, use the JSON type, read fields with ->> '$.path', and index by adding a generated column over the field you filter on. In both, keep columns you join, sort, or constrain as ordinary typed columns and reserve JSON for attributes that genuinely vary per row.

A SaaS product lets each customer define custom fields on a contact: one wants lead_score, another wants region and partner_tier. You are not going to run a migration every time a customer adds a field. This is the textbook case for a JSON column, and both PostgreSQL and MySQL handle it well, as long as you know which type to pick, how the operators behave, and what you cannot index.

Run a sample expression in the JSONPath Query Tester, or explore the same nested data in the JSON Viewer.

PostgreSQL: json vs jsonb

PostgreSQL has two JSON types and the choice is easy. json is a text column with a validity check: it stores your bytes exactly, preserving whitespace, key order, and even duplicate keys, and it re-parses the text on every operation. jsonb parses once on insert into a binary structure that can be indexed and searched without re-parsing.

Use jsonb. The only reason to pick json is a legal or audit requirement to store the request body byte for byte. The duplicate-key row deserves a second look: if an upstream system sends {"status":"a","status":"b"}, jsonb silently keeps "b". The duplicate keys guide shows how other parsers handle the same input and why it matters for security.

Creating the table

CREATE TABLE contacts (
  id          bigserial PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now(),
  attrs       jsonb NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO contacts (email, attrs) VALUES
  ('priya@example.com', '{"plan": "pro", "region": "APAC", "tags": ["vip", "beta"], "score": 87}'),
  ('marco@example.com', '{"plan": "free", "region": "EU", "tags": [], "score": 12}');

Note what is not in the JSON: email and created_at. They are constrained, unique, and sorted on, so they are real columns.

PostgreSQL Operators: ->, ->>, @>, and Friends

The one that trips everyone is -> versus ->>. Single arrow returns jsonb, so attrs -> 'plan' is the JSON value "pro", quotes included, and comparing it to the text 'pro' fails. Double arrow returns text. Chain them: single arrows to walk down, a double arrow at the end.

-- Text comparison: use ->>
SELECT email FROM contacts WHERE attrs ->> 'plan' = 'pro';

-- Numbers come back as text; cast them
SELECT email FROM contacts WHERE (attrs ->> 'score')::int > 50;

-- Nested path
SELECT attrs #>> '{address,city}' FROM contacts;

-- Containment: does attrs include this sub-document?
SELECT email FROM contacts WHERE attrs @> '{"plan": "pro"}';

-- Array membership via containment
SELECT email FROM contacts WHERE attrs @> '{"tags": ["vip"]}';

-- Key existence
SELECT email FROM contacts WHERE attrs ? 'region';

-- Expand an array into rows
SELECT c.email, t.tag
FROM contacts c, jsonb_array_elements_text(c.attrs -> 'tags') AS t(tag);

PostgreSQL 12 added SQL/JSON path queries (jsonb_path_query, @?, @@) with a syntax close to JSONPath, and PostgreSQL 14 added subscripting, so attrs['plan'] works as both a getter and a setter in UPDATE.

Indexing jsonb

GIN index on the whole column

CREATE INDEX contacts_attrs_gin ON contacts USING GIN (attrs);

-- Smaller and faster, but supports only @> (and the path operators):
CREATE INDEX contacts_attrs_gin ON contacts USING GIN (attrs jsonb_path_ops);

The default GIN operator class accelerates @>, ?, ?|, and ?&. The jsonb_path_ops variant drops the existence operators in exchange for a smaller index that is faster for containment. Neither helps with attrs ->> 'plan' = 'pro'; that is a text comparison on an expression, and GIN does not see it. Write it as attrs @> '{"plan": "pro"}' instead, or add the next kind of index.

Expression index on one field

CREATE INDEX contacts_plan_idx ON contacts ((attrs ->> 'plan'));
CREATE INDEX contacts_score_idx ON contacts (((attrs ->> 'score')::int));

-- Uses the index:
SELECT email FROM contacts WHERE attrs ->> 'plan' = 'pro';
SELECT email FROM contacts WHERE (attrs ->> 'score')::int > 50 ORDER BY (attrs ->> 'score')::int;

The expression in the query must match the index expression exactly, including the cast. Run EXPLAIN to confirm an Index Scan; if you see a Seq Scan, the expressions differ.

Updating jsonb: jsonb_set, ||, and -

-- Set or add one key (last arg true = create if missing)
UPDATE contacts
SET attrs = jsonb_set(attrs, '{plan}', '"enterprise"', true)
WHERE email = 'priya@example.com';

-- Shallow merge several keys
UPDATE contacts SET attrs = attrs || '{"verified": true, "region": "EMEA"}';

-- Remove a key
UPDATE contacts SET attrs = attrs - 'legacy_id';

-- Append to an array
UPDATE contacts
SET attrs = jsonb_set(attrs, '{tags}', (attrs -> 'tags') || '["churn-risk"]');

One thing to know about storage: PostgreSQL rewrites the entire jsonb value on any update, and large values are compressed and stored out of line. A table where you flip one boolean inside a 200 KB document thousands of times a minute will generate a lot of write amplification. Keep documents reasonably small or move hot fields to real columns.

MySQL: The JSON Type

MySQL has a single JSON type (since 5.7.8). It validates on insert, stores an internal binary format similar in spirit to jsonb, normalizes key order, and discards insignificant whitespace. Path expressions always start with $.

CREATE TABLE contacts (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  attrs      JSON NOT NULL
);

INSERT INTO contacts (email, attrs) VALUES
  ('priya@example.com', '{"plan": "pro", "region": "APAC", "tags": ["vip", "beta"], "score": 87}');

-- -> is JSON_EXTRACT; ->> is JSON_UNQUOTE(JSON_EXTRACT(...))
SELECT attrs -> '$.plan'  FROM contacts;   -- "pro"  (with quotes, JSON type)
SELECT attrs ->> '$.plan' FROM contacts;   -- pro    (string)

SELECT email FROM contacts WHERE attrs ->> '$.plan' = 'pro';
SELECT email FROM contacts WHERE CAST(attrs ->> '$.score' AS UNSIGNED) > 50;

-- Containment and existence
SELECT email FROM contacts WHERE JSON_CONTAINS(attrs, '"vip"', '$.tags');
SELECT email FROM contacts WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.region');

-- Modify
UPDATE contacts SET attrs = JSON_SET(attrs, '$.plan', 'enterprise', '$.verified', true);
UPDATE contacts SET attrs = JSON_REMOVE(attrs, '$.legacy_id');
UPDATE contacts SET attrs = JSON_ARRAY_APPEND(attrs, '$.tags', 'churn-risk');

JSON_SET inserts or replaces; JSON_INSERT only adds missing paths; JSON_REPLACE only changes existing ones. Picking the wrong one is a quiet bug because none of them error.

Indexing in MySQL: generated columns

MySQL cannot index a JSON column directly. The pattern is to extract the field into a generated column and index that:

ALTER TABLE contacts
  ADD COLUMN plan VARCHAR(32) AS (attrs ->> '$.plan') VIRTUAL,
  ADD INDEX contacts_plan_idx (plan);

-- The optimizer matches the expression, so either form uses the index:
SELECT email FROM contacts WHERE plan = 'pro';
SELECT email FROM contacts WHERE attrs ->> '$.plan' = 'pro';

VIRTUAL columns are computed on read and cost no storage; STORED columns are materialized. Either can be indexed. For arrays, MySQL 8.0.17+ offers multi-valued indexes:

CREATE INDEX contacts_tags_idx ON contacts ((CAST(attrs -> '$.tags' AS CHAR(64) ARRAY)));

SELECT email FROM contacts WHERE 'vip' MEMBER OF (attrs -> '$.tags');

JSON_TABLE: arrays to rows

SELECT c.email, t.tag
FROM contacts c,
     JSON_TABLE(c.attrs, '$.tags[*]' COLUMNS (tag VARCHAR(64) PATH '$')) AS t;

JSON_TABLE turns a JSON array into a relational row set you can join, group, and aggregate. It is the MySQL counterpart of PostgreSQL's jsonb_array_elements.

PostgreSQL vs MySQL JSON: Same Task, Both Syntaxes

JSON Column or Normal Columns?

The JSON column is a trade: schema flexibility in exchange for giving up what relational databases are good at. Put a field in JSON when:

• its presence or shape varies by row (per-tenant custom fields, plugin settings, raw webhook payloads);
• you mostly read it whole and rarely filter on it;
• it changes faster than you want to run migrations.

Keep a field in a typed column when:

• you filter, sort, group, or join on it;
• it needs NOT NULL, a CHECK, a foreign key, or a unique constraint;
• its type matters (dates, decimals, and big integers all degrade inside JSON, as JSON number precision and JSON date formats explain).

The hybrid is the norm: a handful of real columns for identity and hot filters, one jsonb column for the long tail. When a JSON field becomes hot, promote it to a column or a generated column rather than indexing deeper into the document.

Gotchas

• Invalid JSON is rejected at insert time, with an unhelpful position. PostgreSQL says invalid input syntax for type json with a character offset; MySQL says Invalid JSON text. Paste the payload into JSON Prettifier for a line and column before you dig through application code.
• NULL is not null. A SQL NULL column and a JSON null value are different; attrs ->> 'x' returns SQL NULL for both a missing key and a JSON null, so you cannot tell them apart with ->> alone. Use attrs ? 'x' (PostgreSQL) or JSON_CONTAINS_PATH (MySQL) to distinguish.
• Key order is not preserved. Both jsonb and MySQL JSON reorder keys. If a consumer diffs the stored document against the original, use Sort Keys in the formatter on both sides first, or see how to compare two JSON files.
• Numbers come out as text. ->> always yields text, so attrs ->> 'score' > 50 compares strings. Cast, and index the cast expression.
• Schema drift. Nothing stops row 1 from having "score": 87 and row 2 from having "score": "87". Validate against a JSON Schema in the application layer, or use a database-side check: MySQL 8.0.17+ ships JSON_SCHEMA_VALID(), and PostgreSQL has extensions that add the same.
• ORMs can silently stringify. If your JSON column contains a JSON string whose content is JSON ("{\"a\":1}"), something serialized twice. Check the insert path.