SQL
Extract and Query JSON Data from a Column
Explore SQL functions to parse, extract, and filter data stored as JSON strings within a database column (e.g., PostgreSQL/MySQL specific functions).
SELECT
id,
JSON_EXTRACT(settings_json, '$.theme') AS user_theme,
JSON_EXTRACT(settings_json, '$.preferences.notifications') AS email_notifications
FROM
user_configs
WHERE
JSON_EXTRACT(settings_json, '$.status') = 'active';
-- For PostgreSQL, consider using the '->>' operator:
-- SELECT
-- id,
-- settings_json->>'theme' AS user_theme,
-- settings_json->'preferences'->>'notifications' AS email_notifications
-- FROM
-- user_configs
-- WHERE
-- settings_json->>'status' = 'active';
How it works: This snippet illustrates how to interact with JSON data stored in a database column (e.g., `settings_json`). Using `JSON_EXTRACT` (common in MySQL and SQLite) or the `->>` operator (in PostgreSQL), you can extract specific values from the JSON structure using a path. The example shows extracting nested values and also filtering rows based on a value within the JSON data. Note that specific function names and operators for JSON manipulation can vary between different SQL database systems.