SQL Cheatsheet
Postgres
Access property from json object with "->" and "->>"
- -> operator returns JSON object field by key.
- ->> operator returns JSON object field by text.
-- get stakeholder on courier, get nested object SELECT c."courierName", c."data"->'stakeholder'->>'primaryEmail' as "courierEmail" FROM couriers c WHERE c.id = '891e6dc3-141a-4bf6-bbcf-5407009ecefc'
Query from json column that has array of object value
SELECT q.id, j FROM quotes q CROSS JOIN LATERAL JSON_ARRAY_ELEMENTS(q."jsonResponse") as j WHERE j->>'shipmentMode' LIKE 'Roa%';
Get latest left join row
... LEFT JOIN order_adjustment oa ON o.id = oa.order_id AND oa.created_at = ( SELECT MAX(created_at) -- Filter with latest created_at FROM order_adjustment oa2 WHERE oa2.order_id = o.id ) ...
Append array of object on array of object column
-- Append new delta_listing_boxes UPDATE order_line_item SET delta_listing_boxes = ( CASE WHEN delta_listing_boxes IS NULL THEN '[]'::JSONB ELSE delta_listing_boxes END ) || '[{"id": "d9923cc3-dd17-44f7-9f9b-ce4740725ed9","count": null,"weight": 5,"quantity": 4}]'::JSONB WHERE id = '1e7db4b9-658f-4322-9115-a685b3e1946d';
Working with duplicate data and how to update the recent/latest row
-- Common Table Expression or CTE WITH LatestSubscriptions AS ( SELECT ROW_NUMBER() OVER (PARTITION BY cs.company_id, cs.subscription_plan_id ORDER BY cs.starts_at DESC) AS row_num, c.name as company_name, sc.stripe_customer, sp.alias as plan_name, cs.* FROM stripe_customer sc LEFT JOIN company_subscription cs ON cs.company_id = sc.company_id LEFT JOIN subscription_plan sp ON sp.id = cs.subscription_plan_id LEFT JOIN company c ON cs.company_id = c.id WHERE sp.alias IN ('BASE', 'FEATURE_REVERSED_MARKETPLACE', 'FEATURE_REVERSED_MARKETPLACE_SELLER') ) SELECT * FROM LatestSubscriptions; -- Use this when trying to update the rows UPDATE company_subscription SET renews_at = NULL FROM LatestSubscriptions WHERE LatestSubscriptions.row_num > 1 and company_subscription.id = LatestSubscriptions.id;
Enums
Common pattern when working with types or Enum
-- Create enum packaging_type CREATE TYPE packaging_type AS ENUM ('BOX', 'CARTON', 'TRAY'); -- Adding value on enum ALTER TYPE packaging_type ADD VALUE IF NOT EXISTS 'PACKET'; -- Rename carton enum ALTER TYPE packaging_type RENAME VALUE 'CARTON' TO 'CARTONS'; -- List value by array select enum_range(null::packaging_type); -- List value by rows select unnest(enum_range(null, null::packaging_type));
Enums with pg_type
-- Query oid of enum_listing_packaging_type SELECT oid FROM pg_type WHERE typname = 'enum_listing_box_packaging_type' -- Query all data about enum SELECT * FROM pg_type WHERE typname = 'enum_listing_packaging_type' -- List of enum values with oid from pg_type SELECT * FROM pg_enum pe WHERE pe.enumtypid = 94076
Aggregation patterns
Group by column and aggregate all results
-- Create ENUM Type CREATE TYPE packaging_type AS ENUM ('BOX', 'CARTON', 'TRAY'); -- Create Table CREATE TABLE listing_packaging ( id SERIAL PRIMARY KEY, label VARCHAR(255), type packaging_type, company_id UUID ); -- Group by type and aggregate labels SELECT lp."type", array_agg(lp.label) as "labels" FROM listing_packaging lp GROUP BY lp.type; -- Result: Group by type and aggregate labels |type |labels | |-------|---------------------------| |PACKET |{1kg} | |CARTON |{"22L Carton","32L Carton"}| |BOX |{"2kg Box","1kg Box"} | |TRAY |{"18L Tray"} |
Group by column and aggregate if column has value
-- Group by type and aggregate has custom packaging by sum() and case SELECT lp."type", SUM(CASE WHEN lp.company_id IS NOT NULL THEN 1 ELSE 0 END) > 0 AS "has_custom" FROM listing_packaging lp GROUP BY lp.type; -- Result: Group by type and aggregate has custom packaging by sum() and case |type |has_custom| |------|----------| |TRAY |false | |CARTON|false | |PACKET|false | |BOX |true |