SQL
Query JSON Data Stored in a Column (PostgreSQL)
Extract and filter data from JSON columns in PostgreSQL, demonstrating how to access nested properties and array elements within JSON structures using SQL functions.
SELECT
id,
order_details->>'customer_name' AS customer_name,
order_details->'items'->0->>'product_id' AS first_product_id
FROM orders
WHERE order_details->>'status' = 'pending';
How it works: Many modern web applications store semi-structured data in JSON or JSONB columns. This snippet demonstrates how to query such data in PostgreSQL. The `->` operator extracts a JSON object field, and `->>` extracts it as text. Here, it retrieves a customer's name, the ID of the first product in an array, and filters orders where the 'status' property within the JSON column is 'pending'.