JSONPath in SQL Databases

Learning Focus

Modern SQL databases implement JSONPath natively — letting you query deeply nested JSON without extracting it into application code first.

PostgreSQL — SQL/JSON Path

PostgreSQL implements SQL/JSON path (ISO/IEC TR 19075-6):

-- jsonb_path_query: returns JSONB set
SELECT jsonb_path_query(profile, '$.address.city')
FROM users;

-- jsonb_path_exists: boolean check
SELECT name FROM users
WHERE jsonb_path_exists(profile, '$.tags[*] ? (@ == "admin")');

-- jsonb_path_query_first: first match only
SELECT jsonb_path_query_first(profile, '$.scores[*]') AS top_score
FROM users;

Comparison with Operators

-- Using -> and ->> operators (simpler for basic access)
SELECT profile ->> 'email' FROM users;

-- Using JSONPath for complex filters
SELECT * FROM users
WHERE jsonb_path_exists(profile, '$.scores[*] ? (@ > 90)');

MySQL — JSONPath in Functions

MySQL uses JSONPath strings inside built-in functions:

-- JSON_EXTRACT with path
SELECT JSON_EXTRACT(profile, '$.address.city') FROM users;

-- Shorthand ->>
SELECT profile->>'$.address.city' FROM users;

-- Filter with JSON_CONTAINS_PATH
SELECT name FROM users
WHERE JSON_CONTAINS_PATH(profile, 'one', '$.address.city');

-- Check existence of a key
SELECT name FROM users
WHERE JSON_CONTAINS_PATH(profile, 'all', '$.email', '$.phone');

SQLite — JSON Functions

-- json_extract: SQLite equivalent
SELECT json_extract(profile, '$.name') FROM users;

-- json_each: iterate array elements
SELECT value FROM users, json_each(users.profile, '$.tags');

Concept Map

Concept Flow

SQL Database JSON Support
  ├── PostgreSQL JSONB  →  jsonb_path_query / jsonb_path_exists
  ├── MySQL JSON        →  JSON_EXTRACT / ->>
  └── SQLite JSON       →  json_extract / json_each
                              └── All use JSONPath expressions

Common Pitfalls

Pitfall	Consequence	Prevention
Using JSON operators without GIN index	Full table scan	Add CREATE INDEX ... USING GIN (col)
Forgetting ::TEXT cast in PostgreSQL	Comparison fails	Cast: (profile->>'age')::INT > 18
MySQL JSON_EXTRACT returns quoted strings	String comparison mismatch	Use ->> for unquoted text

What's Next

• Previous: Syntax and Operators — Review path expressions.
• Next: jq CLI — Command-line JSON querying.
• Section Overview — Return to the JSONPath module index.