John Jerald De Chavez

SQL Cheatsheet

Postgres

Access property from json object with "->" and "->>"

-- 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      |

References