SQL
Query and Extract Data from JSON Columns
Master extracting specific values and filtering records based on data stored within JSON columns in SQL databases like PostgreSQL, MySQL, or SQL Server.
SELECT id, name,
settings->>'theme' AS user_theme,
settings->'preferences'->>'notifications' AS notifications_status
FROM users
WHERE settings->>'theme' = 'dark';
How it works: With modern databases supporting JSON data types, this snippet shows how to query and extract information from JSON columns. Using operators like `->` (for JSON object field as JSON) and `->>` (for JSON object field as text) in PostgreSQL, you can navigate nested JSON structures and retrieve specific values. The `WHERE` clause also demonstrates how to filter records based on values within the JSON column, which is highly useful for dynamic user preferences or configurable data.