SQL
Querying and Extracting JSON Data in PostgreSQL and MySQL
Learn to query and extract data from JSON columns in your database using native SQL JSON functions, enabling flexible data storage for web apps.
-- Example Table: products
-- +----+----------+-------------------------------------------------+
-- | id | name | details |
-- +----+----------+-------------------------------------------------+
-- | 1 | Laptop X | {"weight": 1.5, "specs": {"cpu": "i7", "ram": "16GB"}, "tags": ["electronics", "sale"]}|
-- | 2 | Phone Y | {"weight": 0.2, "specs": {"cpu": "A15", "ram": "8GB"}, "tags": ["mobile", "new"]}|
-- +----+----------+-------------------------------------------------+
-- PostgreSQL JSONB functions:
-- Extract a top-level key
SELECT id, name, details->>'weight' AS product_weight
FROM products
WHERE details->>'weight' = '1.5';
-- Extract a nested key
SELECT id, name, details->'specs'->>'cpu' AS cpu_spec
FROM products
WHERE details->'specs'->>'cpu' = 'i7';
-- Check for array containment
SELECT id, name
FROM products
WHERE details->'tags' ? 'sale';
-- MySQL JSON functions:
-- Extract a top-level key
SELECT id, name, JSON_EXTRACT(details, '$.weight') AS product_weight
FROM products
WHERE JSON_EXTRACT(details, '$.weight') = 1.5;
-- Extract a nested key
SELECT id, name, JSON_EXTRACT(details, '$.specs.cpu') AS cpu_spec
FROM products
WHERE JSON_EXTRACT(details, '$.specs.cpu') = 'i7';
-- Check for array containment (JSON_CONTAINS)
SELECT id, name
FROM products
WHERE JSON_CONTAINS(details, '"sale"', '$.tags');
How it works: Modern SQL databases like PostgreSQL (with JSONB type) and MySQL (with JSON type) provide powerful native functions to store, query, and manipulate JSON data directly within columns. This allows for schema flexibility without entirely abandoning relational structures. The snippets demonstrate extracting values, accessing nested properties, and querying for array containment, which is extremely useful for handling dynamic attributes or semi-structured data common in web applications.