---
# === IDENTITY ===
id: software/patterns/sql-json-queries/2026
canonical_question: "How do I query JSON data in PostgreSQL and MySQL?"
aliases:
  - "PostgreSQL JSON query"
  - "MySQL JSON_EXTRACT"
  - "jsonb operators PostgreSQL"
  - "SQL JSON functions"
  - "PostgreSQL jsonb GIN index"
  - "MySQL JSON_TABLE"
  - "query JSON column SQL"
  - "PostgreSQL -> vs ->> operator"
  - "jsonpath PostgreSQL"
  - "MySQL multi-valued index JSON"
entity_type: software_reference
domain: software > patterns > sql_json_queries
region: global
jurisdiction: global
temporal_scope: 2015-2026

# === VERIFICATION ===
last_verified: 2026-02-23
confidence: 0.94
version: 1.0
first_published: 2026-02-23

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: "PostgreSQL 17 added SQL/JSON standard functions JSON_EXISTS(), JSON_QUERY(), JSON_VALUE() (2024-09)"
  next_review: 2026-08-22
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "PostgreSQL GIN indexes only work on jsonb columns, not json — cast or migrate to jsonb before indexing"
  - "MySQL JSON columns cannot be indexed directly — use generated columns or multi-valued indexes on arrays"
  - "PostgreSQL -> returns jsonb; ->> returns text — mixing them up causes type-mismatch errors in WHERE clauses"
  - "JSON_TABLE in MySQL requires 8.0.4+; in PostgreSQL it requires 17+ — check version before recommending"
  - "jsonb stores data in decomposed binary format — key order is not preserved, duplicate keys are removed (last value wins)"
  - "MySQL JSON_EXTRACT returns JSON-typed values with quotes — use JSON_UNQUOTE() or ->> to get raw text"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Need to store/retrieve entire JSON documents without querying inside them"
    use_instead: "Use a TEXT or json (not jsonb) column — avoids parsing overhead"
  - condition: "Working with MongoDB or a document database, not SQL"
    use_instead: "MongoDB query documentation — different query language entirely"
  - condition: "Need full-text search inside JSON string values"
    use_instead: "PostgreSQL tsvector with jsonb_to_tsvector() or dedicated search engine (Elasticsearch)"

# === AGENT HINTS ===
inputs_needed:
  - key: "database_engine"
    question: "Which database are you using?"
    type: choice
    options: ["PostgreSQL", "MySQL"]
  - key: "postgresql_version"
    question: "What PostgreSQL version? (9.4+, 12+, 15+, 17+)"
    type: choice
    options: ["9.4-11", "12-14", "15-16", "17+"]
  - key: "query_type"
    question: "What do you need to do with the JSON data?"
    type: choice
    options: ["Extract a value", "Filter/search", "Aggregate", "Join/flatten arrays", "Index for performance"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/patterns/sql-json-queries/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-23)"

# === RELATED UNITS ===
related_kos:
  depends_on: []
  related_to:
    - id: "software/debugging/postgresql-slow-queries/2026"
      label: "How to Diagnose and Optimize Slow PostgreSQL Queries"
    - id: "software/debugging/postgresql-connection-pool/2026"
      label: "PostgreSQL Connection Pool Exhaustion"
  solves: []
  alternative_to: []
  often_confused_with:
    - id: "software/migrations/mysql-to-postgresql/2026"
      label: "MySQL to PostgreSQL Migration Guide"

# === SOURCES (7 authoritative sources) ===
sources:
  - id: src1
    title: "JSON Functions and Operators"
    author: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/functions-json.html
    type: official_docs
    published: 2025-09-01
    reliability: authoritative
  - id: src2
    title: "The JSON Data Type"
    author: Oracle / MySQL
    url: https://dev.mysql.com/doc/refman/8.4/en/json.html
    type: official_docs
    published: 2024-04-01
    reliability: authoritative
  - id: src3
    title: "Indexing JSONB in Postgres"
    author: Crunchy Data
    url: https://www.crunchydata.com/blog/indexing-jsonb-in-postgres
    type: technical_blog
    published: 2024-06-15
    reliability: high
  - id: src4
    title: "JSON Table Functions — JSON_TABLE"
    author: Oracle / MySQL
    url: https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html
    type: official_docs
    published: 2024-04-01
    reliability: authoritative
  - id: src5
    title: "PostgreSQL Anti-Patterns: Unnecessary JSON/Hstore Dynamic Columns"
    author: EDB / 2ndQuadrant
    url: https://www.enterprisedb.com/blog/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns
    type: technical_blog
    published: 2023-03-10
    reliability: high
  - id: src6
    title: "Understanding Postgres GIN Indexes: The Good and the Bad"
    author: pganalyze
    url: https://pganalyze.com/blog/gin-index
    type: technical_blog
    published: 2024-01-20
    reliability: high
  - id: src7
    title: "Indexing JSON Data in MySQL"
    author: Oracle / MySQL Blog
    url: https://blogs.oracle.com/mysql/indexing-json-data-in-mysql
    type: technical_blog
    published: 2023-08-15
    reliability: high
---

# How to Query JSON Data in PostgreSQL and MySQL

## TL;DR

- **Bottom line**: PostgreSQL uses `jsonb` with operators (`->`, `->>`, `@>`, `@?`) and GIN indexes for fast JSON queries; MySQL uses `JSON_EXTRACT()` / `->` / `->>` with generated columns or multi-valued indexes. PostgreSQL jsonb is significantly more powerful for in-database JSON work.
- **Key tool/command**: `SELECT data->>'name' FROM t WHERE data @> '{"status":"active"}'::jsonb` (PostgreSQL) or `SELECT data->>'$.name' FROM t WHERE JSON_EXTRACT(data, '$.status') = '"active"'` (MySQL)
- **Watch out for**: Using `->` when you need `->>` (or vice versa) — `->` returns JSON type, `->>` returns text. This causes silent type mismatches in WHERE clauses.
- **Works with**: PostgreSQL 9.4+ (jsonb), 12+ (jsonpath), 17+ (SQL/JSON standard); MySQL 5.7+ (JSON type), 8.0+ (JSON_TABLE, multi-valued indexes).

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- **GIN indexes require jsonb, not json** — PostgreSQL `json` type cannot be indexed with GIN. Always use `jsonb` for queried columns. [src1]
- **MySQL JSON columns cannot be directly indexed** — create a generated (virtual or stored) column and index that, or use multi-valued indexes for arrays (MySQL 8.0.17+). [src7]
- **`->` returns JSON/jsonb; `->>` returns text** — comparing `data->'status'` to a plain string fails silently. Use `->>` for text comparisons. [src1]
- **JSON_TABLE requires specific versions** — MySQL 8.0.4+ and PostgreSQL 17+. Do not recommend to users on older versions. [src4]
- **jsonb normalizes data** — key order is not preserved, whitespace is stripped, duplicate keys keep only the last value. If exact text preservation is required, use `json` type in PostgreSQL. [src1]
- **MySQL JSON_EXTRACT returns quoted strings** — `JSON_EXTRACT(data, '$.name')` returns `"John"` (with quotes). Use `JSON_UNQUOTE()` or `->>` to get unquoted text. [src2]

## Quick Reference

| Scenario | PostgreSQL (jsonb) | MySQL (JSON) | Key Difference |
|---|---|---|---|
| Extract field as JSON | `data->'key'` | `JSON_EXTRACT(data, '$.key')` or `data->'$.key'` | PG operator is shorter; MySQL needs `$` path prefix |
| Extract field as text | `data->>'key'` | `data->>'$.key'` or `JSON_UNQUOTE(JSON_EXTRACT(...))` | Both `->>` return text; MySQL syntax added in 5.7.13 |
| Nested access | `data->'a'->'b'->>'c'` | `data->>'$.a.b.c'` | PG chains operators; MySQL uses dot-path in one call |
| Path access | `data #>> '{a,b,c}'` | `JSON_EXTRACT(data, '$.a.b.c')` | PG `#>>` uses array path; MySQL uses dot notation |
| Containment check | `data @> '{"k":"v"}'::jsonb` | `JSON_CONTAINS(data, '"v"', '$.k')` | PG `@>` is GIN-indexable; MySQL needs generated column |
| Key existence | `data ? 'key'` | `JSON_CONTAINS_PATH(data, 'one', '$.key')` | PG `?` uses GIN index directly |
| Array element search | `data @> '[3]'::jsonb` | `3 MEMBER OF(data->'$.arr')` | MySQL MEMBER OF uses multi-valued index (8.0.17+) |
| Flatten array to rows | `jsonb_array_elements(data->'arr')` | `JSON_TABLE(data, '$.arr[*]' COLUMNS(...))` | PG uses set-returning function; MySQL uses table function |
| jsonpath filter | `jsonb_path_query(data, '$.items[*] ? (@.price > 10)')` | N/A — no jsonpath support | PostgreSQL 12+ only; MySQL has no equivalent |
| SQL/JSON standard | `JSON_VALUE(data, '$.name')` | N/A | PostgreSQL 17+; MySQL does not implement SQL/JSON standard |
| GIN index (general) | `CREATE INDEX ON t USING GIN (data)` | Not supported on JSON columns | PG indexes full jsonb; MySQL needs generated columns |
| GIN index (containment) | `CREATE INDEX ON t USING GIN (data jsonb_path_ops)` | N/A | Smaller index, supports `@>` only |
| Multi-valued index | N/A | `CREATE INDEX ON t ((CAST(data->'$.tags' AS UNSIGNED ARRAY)))` | MySQL 8.0.17+ for JSON arrays |
| Aggregate JSON | `jsonb_agg()`, `jsonb_object_agg()` | `JSON_ARRAYAGG()`, `JSON_OBJECTAGG()` | Both build JSON from result sets |
| Modify JSON | `jsonb_set(data, '{key}', '"val"')` | `JSON_SET(data, '$.key', 'val')` | PG returns new jsonb; MySQL modifies in-place with UPDATE |

## Decision Tree

> Full script: [decision-tree.txt](scripts/decision-tree.txt) (40 lines)

```
START
├── Which database?
│   ├── PostgreSQL ↓
│   │   ├── Column type is json (not jsonb)?
│   │   │   ├── YES → ALTER TABLE t ALTER COLUMN data TYPE jsonb USING data::jsonb
# ... (see full script)
```

## Step-by-Step Guide

### 1. Choose the right column type

PostgreSQL offers two JSON types. Use `jsonb` for any column you will query. The `json` type stores raw text and cannot be indexed or efficiently searched. [src1]

```sql
-- PostgreSQL: create table with jsonb column
CREATE TABLE events (
    id     SERIAL PRIMARY KEY,
    data   jsonb NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- MySQL: create table with JSON column
CREATE TABLE events (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    data   JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

**Verify**: `SELECT pg_typeof(data) FROM events LIMIT 1;` --> expected output: `jsonb`

### 2. Insert JSON data

Both databases accept JSON strings for insertion. PostgreSQL validates and converts to binary on insert; MySQL validates JSON syntax. [src1] [src2]

```sql
-- PostgreSQL
INSERT INTO events (data) VALUES
('{"type": "purchase", "amount": 49.99, "tags": ["web", "mobile"], "user": {"id": 42, "name": "Alice"}}');

-- MySQL
INSERT INTO events (data) VALUES
('{"type": "purchase", "amount": 49.99, "tags": ["web", "mobile"], "user": {"id": 42, "name": "Alice"}}');
```

**Verify**: `SELECT data->>'type' FROM events;` --> expected output: `purchase`

### 3. Extract values from JSON

Use the arrow operators for extraction. The single arrow (`->`) returns JSON type; double arrow (`->>`) returns text. [src1] [src2]

```sql
-- PostgreSQL: extract nested value as text
SELECT data->>'type' AS event_type,
       data->'user'->>'name' AS user_name,
       (data->>'amount')::numeric AS amount
FROM events;

-- MySQL: extract nested value as text
SELECT data->>'$.type' AS event_type,
       data->>'$.user.name' AS user_name,
       CAST(JSON_EXTRACT(data, '$.amount') AS DECIMAL(10,2)) AS amount
FROM events;
```

**Verify**: Both return `purchase | Alice | 49.99`

### 4. Filter rows using JSON predicates

PostgreSQL's containment operator (`@>`) is the most efficient for GIN-indexed queries. MySQL requires generated columns or explicit function calls. [src3] [src7]

```sql
-- PostgreSQL: containment (GIN-indexable)
SELECT * FROM events
WHERE data @> '{"type": "purchase"}'::jsonb;

-- PostgreSQL: jsonpath filter (12+)
SELECT * FROM events
WHERE jsonb_path_exists(data, '$.tags[*] ? (@ == "mobile")');

-- MySQL: function-based filter
SELECT * FROM events
WHERE JSON_EXTRACT(data, '$.type') = '"purchase"';

-- MySQL: array membership (8.0.17+)
SELECT * FROM events
WHERE 'mobile' MEMBER OF(data->'$.tags');
```

**Verify**: All queries return the inserted purchase event row.

### 5. Create indexes for JSON queries

Without indexes, every JSON query requires a full table scan. Index strategy differs significantly between the two databases. [src3] [src6] [src7]

```sql
-- PostgreSQL: GIN index on entire jsonb column (supports @>, ?, ?|, ?&, @?)
CREATE INDEX idx_events_data ON events USING GIN (data);

-- PostgreSQL: GIN with jsonb_path_ops (smaller, supports @> only)
CREATE INDEX idx_events_data_pathops ON events USING GIN (data jsonb_path_ops);

-- PostgreSQL: B-tree expression index on a specific key (fastest for single-key filters)
CREATE INDEX idx_events_type ON events ((data->>'type'));

-- MySQL: generated column + index (the standard pattern)
ALTER TABLE events
    ADD COLUMN event_type VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(data->'$.type')) STORED,
    ADD INDEX idx_event_type (event_type);

-- MySQL: multi-valued index on JSON array (8.0.17+)
ALTER TABLE events
    ADD INDEX idx_events_tags ((CAST(data->'$.tags' AS CHAR(50) ARRAY)));
```

**Verify**: `EXPLAIN (ANALYZE) SELECT * FROM events WHERE data @> '{"type":"purchase"}'::jsonb;` --> should show `Bitmap Index Scan on idx_events_data`

### 6. Flatten JSON arrays to rows

Both databases can convert JSON arrays into relational rows, but with different syntax. [src1] [src4]

```sql
-- PostgreSQL: jsonb_array_elements (any version with jsonb)
SELECT e.id,
       elem->>'tag' AS tag
FROM events e,
     jsonb_array_elements(e.data->'tags') WITH ORDINALITY AS arr(elem, pos);

-- PostgreSQL: simpler for text arrays
SELECT e.id, tag
FROM events e,
     jsonb_array_elements_text(e.data->'tags') AS tag;

-- MySQL: JSON_TABLE (8.0.4+)
SELECT e.id, jt.tag
FROM events e,
     JSON_TABLE(e.data, '$.tags[*]'
         COLUMNS(tag VARCHAR(100) PATH '$')
     ) AS jt;
```

**Verify**: Both return one row per tag: `1 | web` and `1 | mobile`

## Code Examples

### PostgreSQL: Complete jsonb Query Patterns

> Full script: [postgresql-complete-jsonb-query-patterns.sql](scripts/postgresql-complete-jsonb-query-patterns.sql) (30 lines)

```sql
-- Input:  events table with jsonb data column
-- Output: filtered, extracted, aggregated JSON data
-- 1. Extract + filter + cast in one query
SELECT data->>'type' AS event_type,
       (data->>'amount')::numeric AS amount,
# ... (see full script)
```

### MySQL: Complete JSON Query Patterns

> Full script: [mysql-complete-json-query-patterns.sql](scripts/mysql-complete-json-query-patterns.sql) (31 lines)

```sql
-- Input:  events table with JSON data column
-- Output: filtered, extracted, aggregated JSON data
-- 1. Extract + filter with generated column
SELECT data->>'$.type' AS event_type,
       CAST(JSON_EXTRACT(data, '$.amount') AS DECIMAL(10,2)) AS amount,
# ... (see full script)
```

### Python (psycopg2/mysql-connector): Application-Level JSON Queries

```python
# Input:  Database connection, JSON query parameters
# Output: Filtered results from JSON columns

import psycopg2
import json

# PostgreSQL with psycopg2
conn = psycopg2.connect("dbname=mydb")
cur = conn.cursor()

# Safe parameterized containment query
filter_obj = {"type": "purchase"}
cur.execute(
    "SELECT id, data->>'type', (data->>'amount')::numeric "
    "FROM events WHERE data @> %s::jsonb",
    (json.dumps(filter_obj),)
)
for row in cur.fetchall():
    print(f"id={row[0]}, type={row[1]}, amount={row[2]}")

cur.close()
conn.close()
```

## Anti-Patterns

### Wrong: Using json type and trying to index it

```sql
-- BAD — json type cannot use GIN indexes, every query does a full table scan
CREATE TABLE events (id SERIAL, data json NOT NULL);
CREATE INDEX idx_data ON events USING GIN (data);  -- ERROR: no operator class for json
```

### Correct: Use jsonb for queryable JSON data

```sql
-- GOOD — jsonb supports GIN indexes and all operators
CREATE TABLE events (id SERIAL, data jsonb NOT NULL);
CREATE INDEX idx_data ON events USING GIN (data);  -- works perfectly
```

### Wrong: Comparing -> output to text string

```sql
-- BAD — data->'status' returns jsonb, not text; this compares jsonb to text
SELECT * FROM events WHERE data->'status' = 'active';
-- Returns 0 rows even when matching rows exist
```

### Correct: Use ->> for text comparison or cast the literal

```sql
-- GOOD — ->> returns text
SELECT * FROM events WHERE data->>'status' = 'active';
-- OR — compare jsonb to jsonb
SELECT * FROM events WHERE data->'status' = '"active"'::jsonb;
```

### Wrong: MySQL JSON_EXTRACT without unquoting

```sql
-- BAD — JSON_EXTRACT returns '"active"' (with quotes), comparison fails
SELECT * FROM events WHERE JSON_EXTRACT(data, '$.status') = 'active';
-- Returns 0 rows because '"active"' != 'active'
```

### Correct: Use ->> or JSON_UNQUOTE in MySQL

```sql
-- GOOD — ->> unquotes automatically
SELECT * FROM events WHERE data->>'$.status' = 'active';
-- OR
SELECT * FROM events WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$.status')) = 'active';
```

### Wrong: Storing relational data in JSON blobs

```sql
-- BAD — every row has the same keys, no constraints, no foreign keys
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    data jsonb -- {"name": "Alice", "email": "a@b.com", "role_id": 3}
);
-- Cannot enforce NOT NULL on email, no FK to roles table, no CHECK constraints
```

### Correct: Use columns for structured data, JSON for flexible data

```sql
-- GOOD — fixed fields are columns, variable metadata is jsonb
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    role_id INT REFERENCES roles(id),
    metadata jsonb DEFAULT '{}'  -- preferences, settings, custom fields
);
```

### Wrong: No index on frequently queried JSON paths

```sql
-- BAD — full table scan on every query
SELECT * FROM events WHERE data @> '{"type": "purchase"}'::jsonb;
-- Seq Scan on events  (cost=0.00..12345.00 rows=1000000 width=200)
```

### Correct: Add appropriate GIN or expression index

```sql
-- GOOD — GIN index enables bitmap index scan
CREATE INDEX idx_events_data ON events USING GIN (data jsonb_path_ops);
-- Bitmap Index Scan on idx_events_data  (cost=0.00..4.50 rows=50 width=200)
```

## Common Pitfalls

- **GIN index bloat from frequent updates**: GIN indexes on frequently updated jsonb columns grow and accumulate dead entries. Fix: `REINDEX INDEX CONCURRENTLY idx_name;` periodically, or use expression B-tree indexes for write-heavy columns. [src6]
- **work_mem too low for GIN bitmap scans**: GIN queries build bitmaps in memory. If `work_mem` is too low, PostgreSQL spills to disk. Fix: `SET work_mem = '64MB';` for the session or tune globally. [src6]
- **MySQL VIRTUAL vs STORED generated columns**: Virtual columns are not materialized — indexes on virtual columns still work but may be slower for complex expressions. Fix: Use `STORED` for complex JSON extractions that are indexed and queried frequently. [src7]
- **PostgreSQL jsonb_set does not create intermediate keys**: `jsonb_set('{}', '{a,b}', '"v"')` fails if key `a` does not exist. Fix: Use `jsonb_set` with `create_if_missing := true` (PG 16+) or build the nested structure first. [src1]
- **Casting JSON numbers**: PostgreSQL `(data->>'price')::numeric` works, but MySQL `CAST(JSON_EXTRACT(data,'$.price') AS DECIMAL(10,2))` requires explicit precision. Omitting precision defaults to `DECIMAL(10,0)` — truncating decimals silently. Fix: Always specify precision in MySQL CAST. [src2]
- **Large JSON documents degrade performance**: Documents over 1MB slow down both reads and writes. The entire jsonb value is rewritten on any update. Fix: Normalize large arrays into separate rows; keep jsonb documents under 1MB. [src5]
- **PostgreSQL json_agg vs jsonb_agg**: `json_agg` returns `json` type; `jsonb_agg` returns `jsonb`. Mixing types causes implicit casts and performance overhead. Fix: Use `jsonb_agg` consistently if your columns are `jsonb`. [src1]
- **MySQL JSON_OVERLAPS false positives**: `JSON_OVERLAPS` compares arrays element-by-element but type-coerces, so `JSON_OVERLAPS('[1]', '["1"]')` returns true (integer 1 matches string "1"). Fix: Ensure consistent types in JSON arrays. [src2]

## Diagnostic Commands

```sql
-- PostgreSQL: check if GIN index is being used
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM events WHERE data @> '{"type": "purchase"}'::jsonb;

-- PostgreSQL: check jsonb column size distribution
SELECT pg_column_size(data) AS bytes,
       COUNT(*) AS row_count
FROM events
GROUP BY 1 ORDER BY 1 DESC LIMIT 20;

-- PostgreSQL: find unindexed jsonb columns
SELECT schemaname, tablename, attname
FROM pg_stats
WHERE atttypid = 'jsonb'::regtype
  AND tablename NOT IN (
    SELECT indrelid::regclass::text FROM pg_index
    WHERE indkey::text LIKE '%' || attnum::text || '%'
  );

-- MySQL: check JSON column usage with EXPLAIN
EXPLAIN FORMAT=JSON
SELECT * FROM events WHERE data->>'$.type' = 'purchase';

-- MySQL: verify multi-valued index usage
EXPLAIN SELECT * FROM events
WHERE 'mobile' MEMBER OF(data->'$.tags');
-- Look for: "Using index" in Extra column
```

## Version History & Compatibility

| Version | Status | JSON Features | Key Changes |
|---|---|---|---|
| PostgreSQL 17+ | Current | SQL/JSON standard: `JSON_EXISTS`, `JSON_QUERY`, `JSON_VALUE`, `JSON_TABLE` | First SQL/JSON standard compliance; jsonpath improvements |
| PostgreSQL 15-16 | Supported | `IS JSON` predicate, `JSON_SCALAR`, merge patch | `jsonb_set` `create_if_missing`, `MERGE` statement |
| PostgreSQL 12-14 | Supported | jsonpath (`@?`, `@@`), `jsonb_path_query` | SQL/JSON path language introduced in 12 |
| PostgreSQL 9.4-11 | Legacy | jsonb type, GIN indexes, `@>`, `?`, `->`, `->>` | jsonb introduced in 9.4; mature by 9.6 |
| MySQL 8.0.17+ | Current | Multi-valued indexes, `MEMBER OF`, `JSON_OVERLAPS` | Array indexing via `CAST(... AS ... ARRAY)` |
| MySQL 8.0.4+ | Current | `JSON_TABLE`, `JSON_ARRAYAGG`, `JSON_OBJECTAGG` | Table function for flattening JSON |
| MySQL 5.7+ | Legacy | `JSON_EXTRACT`, `->`, `->>`, `JSON_CONTAINS` | JSON type introduced; basic operators only |

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Schema-less or semi-structured data (user preferences, metadata, feature flags) | Every row has identical fields (name, email, created_at) | Regular columns with proper types and constraints |
| Third-party API responses stored for later processing | Data needs foreign key constraints and referential integrity | Normalized relational tables |
| Rapid prototyping where schema changes frequently | Complex aggregations, GROUP BY, and analytical queries on JSON fields | Extract to columns, then aggregate |
| Dynamic form data or custom fields per tenant | Write-heavy workloads with frequent partial JSON updates | Separate columns or EAV pattern |
| PostgreSQL with jsonb + GIN for search within JSON | MySQL and need advanced JSON querying (jsonpath, SQL/JSON) | PostgreSQL, or extract to generated columns in MySQL |

## Important Caveats

- PostgreSQL jsonb equality (`=`) compares entire documents — two documents with the same keys in different order are equal, but this is an expensive comparison. Use specific key comparisons when possible.
- MySQL does not support the SQL/JSON standard (`JSON_TABLE` is MySQL-specific, not the same as SQL:2016 `JSON_TABLE`). PostgreSQL 17+ follows the SQL/JSON standard more closely.
- GIN index creation locks the table in PostgreSQL. Use `CREATE INDEX CONCURRENTLY` to avoid blocking writes, but it takes 2-3x longer and cannot run inside a transaction.
- jsonb column statistics are not maintained by PostgreSQL's planner — complex jsonb WHERE clauses may get poor row estimates. Consider expression indexes or materialized views for critical queries.
- MySQL's JSON comparison rules differ from PostgreSQL: MySQL type-coerces during comparison (integer `1` equals string `"1"` in some contexts), while PostgreSQL is strict about types.

## Related Units

- [How to Diagnose and Optimize Slow PostgreSQL Queries](/software/debugging/postgresql-slow-queries/2026)
- [PostgreSQL Connection Pool Exhaustion](/software/debugging/postgresql-connection-pool/2026)
- [MySQL to PostgreSQL Migration Guide](/software/migrations/mysql-to-postgresql/2026)