SQL
Querying and Manipulating JSON Data in SQL Databases
Discover how to effectively query, extract, and manipulate JSON data stored within your SQL database using native JSON functions, simplifying complex data handling for web applications.
-- Assuming a table 'users' with a JSONB column 'profile_data'
-- Example 1: Extract a specific value
SELECT
id,
profile_data ->> 'name' AS user_name,
profile_data -> 'address' ->> 'city' AS user_city
FROM
users
WHERE
profile_data ->> 'status' = 'active';
-- Example 2: Update a JSON field (PostgreSQL)
UPDATE users
SET
profile_data = jsonb_set(profile_data, '{email}', '"[email protected]"', false)
WHERE
id = 1;
-- Example 3: Check for key existence (PostgreSQL)
SELECT id, profile_data
FROM users
WHERE profile_data ? 'phone';
-- MySQL equivalent for extraction:
-- SELECT id, JSON_EXTRACT(profile_data, '$.name') AS user_name FROM users;
How it works: Modern SQL databases (like PostgreSQL with JSONB, MySQL with JSON) provide powerful functions to store, query, and manipulate JSON data directly within columns. This allows for flexible schema design. Functions like `->>` (PostgreSQL for text extraction), `->` (PostgreSQL for JSON object/array), `JSON_EXTRACT` (MySQL), `JSON_SET` (MySQL), `jsonb_set` (PostgreSQL), and operators like `?` (PostgreSQL for key existence) are essential for interacting with this data effectively.