SQL
Query JSONB Data with PostgreSQL Functions
Explore powerful PostgreSQL functions like ->>, jsonb_array_elements, and jsonb_each to effectively query and manipulate JSONB column data.
-- Assuming a table 'orders' with a 'details' jsonb column
-- Example 1: Extract a specific field
SELECT id, details->>'customer_name' AS customer
FROM orders
WHERE details->'items'->0->>'product_id' = 'PID001';
-- Example 2: Query within a JSON array
SELECT id, customer_name, item_product_id
FROM orders,
jsonb_each_text(details->'customer') AS (customer_name_key, customer_name),
jsonb_array_elements(details->'items') AS item,
jsonb_each_text(item) AS (item_key, item_product_id)
WHERE item_key = 'product_id' AND item_product_id = 'PID001';
-- Example 3: Check for key existence or value
SELECT id, details
FROM orders
WHERE details ? 'customer_name' AND details @> '{"status": "completed"}';
How it works: This snippet demonstrates various ways to query and manipulate jsonb data types in PostgreSQL, which are increasingly common in web development for flexible schemas. It shows how to extract specific fields (->>), query elements within JSON arrays (jsonb_array_elements), and check for the existence of keys (?) or specific values (@>). These functions are invaluable for working with semi-structured data directly within your database queries.