SQL
Extract Values from PostgreSQL JSONB Columns
Learn to efficiently query and extract specific values from JSONB columns in PostgreSQL using operators like `->>` and `@>`, enabling advanced filtering and data retrieval.
SELECT
id,
product_info ->> 'name' AS product_name,
CAST(product_info ->> 'price' AS NUMERIC) AS product_price
FROM
products
WHERE
product_info @> '{"category": "Electronics"}'
ORDER BY
product_price DESC;
How it works: This PostgreSQL query demonstrates extracting specific fields from a `JSONB` column named `product_info`. The `->>` operator extracts a JSON field as text, while `CAST` converts the extracted `price` to a numeric type. The `@>` operator efficiently checks if the `product_info` JSONB column contains a specific JSON sub-object, enabling powerful filtering of records based on their JSON content.