SQL
Extracting Specific Values from JSON Columns in SQL
Learn to effectively query and extract nested data from JSON document columns in your SQL database, enhancing flexibility for schema-less data.
-- Example for MySQL (JSON_EXTRACT or -> and ->> operators)
SELECT
id,
name,
data->>'$.preferences.theme' AS user_theme,
data->'$.settings' AS user_settings_object
FROM profiles
WHERE data->>'$.preferences.notifications' = 'enabled';
-- Equivalent for PostgreSQL (using -> and ->> operators)
-- SELECT
-- id,
-- name,
-- data->'preferences'->>'theme' AS user_theme,
-- data->'settings' AS user_settings_object
-- FROM profiles
-- WHERE data->'preferences'->>'notifications' = 'enabled';
How it works: Modern SQL databases like MySQL and PostgreSQL offer robust support for JSON data types. This snippet shows how to query and extract specific values from a JSON column named `data`. For MySQL, `->>'$.path'` extracts a string value, and `->'$.path'` extracts a JSON object or array. For PostgreSQL, `->'key'` navigates to an object key, and `->>'key'` extracts its text value. The `WHERE` clause demonstrates filtering based on a JSON field's value, enabling flexible querying of semi-structured data.