SQL
Query and Manipulate JSON Data in SQL
Learn to store, query, and extract specific data from JSON columns using native SQL functions available in modern databases, enhancing flexibility for semi-structured data.
-- PostgreSQL Example
-- Table creation with JSONB column
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
event_data JSONB
);
INSERT INTO events (event_data) VALUES
('{ "type": "purchase", "user": { "id": 101, "name": "Alice" }, "product": "Book", "price": 25.99 }'),
('{ "type": "view", "user": { "id": 102, "name": "Bob" }, "page": "/home" }'),
('{ "type": "purchase", "user": { "id": 103, "name": "Charlie" }, "product": "Laptop", "price": 1200.00 }');
-- Select specific JSON fields
SELECT
event_id,
event_data ->> 'type' AS event_type,
event_data -> 'user' ->> 'name' AS user_name,
event_data ->> 'price' AS product_price -- Returns text, needs casting for numeric ops
FROM
events
WHERE
event_data ->> 'type' = 'purchase'
AND (event_data ->> 'price')::numeric > 50;
-- MySQL Example (8.0+)
-- Table creation with JSON column
-- CREATE TABLE events (
-- event_id INT AUTO_INCREMENT PRIMARY KEY,
-- event_data JSON
-- );
-- INSERT INTO events (event_data) VALUES
-- ('{ "type": "purchase", "user": { "id": 101, "name": "Alice" }, "product": "Book", "price": 25.99 }'),
-- ('{ "type": "view", "user": { "id": 102, "name": "Bob" }, "page": "/home" }'),
-- ('{ "type": "purchase", "user": { "id": 103, "name": "Charlie" }, "product": "Laptop", "price": 1200.00 }');
-- Select specific JSON fields
-- SELECT
-- event_id,
-- JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.type')) AS event_type,
-- JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.user.name')) AS user_name,
-- JSON_EXTRACT(event_data, '$.price') AS product_price
-- FROM
-- events
-- WHERE
-- JSON_EXTRACT(event_data, '$.type') = '"purchase"'
-- AND JSON_EXTRACT(event_data, '$.price') > 50;
How it works: This snippet demonstrates how to store and query JSON data directly within SQL. It provides examples for PostgreSQL (using `JSONB` type and `->>` operators) and comments out a similar approach for MySQL (using `JSON` type and `JSON_EXTRACT()`, `JSON_UNQUOTE()` functions). You can extract scalar values, navigate nested objects, and filter records based on values within the JSON document, making it flexible for handling semi-structured data without strict schema definitions.