SQL
Querying JSON Data in PostgreSQL
Extract and query data from JSONB columns in PostgreSQL using operators like `->>` and `->`. This is essential for modern applications storing flexible, schemaless data structures.
SELECT id, data->>'name' AS user_name, data->'address'->>'city' AS user_city
FROM users
WHERE data->>'status' = 'active'
ORDER BY data->'address'->>'city';
How it works: This snippet demonstrates how to interact with JSONB (or JSON) columns in PostgreSQL. The `->>` operator extracts a JSON object field as text, while `->` extracts a JSON object field as JSON. This allows you to select specific values from a JSON document, even nested ones, and use them in `WHERE` clauses for filtering or `ORDER BY` for sorting, treating the flexible JSON data like regular columns.