CREATE TABLE Business(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    zipcodes TEXT[] NOT NULL,
    headcounts INTEGER[] NOT NULL
);

CREATE TABLE Users (
   name TEXT NOT NULL,
   age INTEGER NOT NULL
);

select:
SELECT name, location.headcount, location.zipcode
FROM Business, UNNEST(zipcodes, headcounts) AS location(zipcode, headcount);

counts:
SELECT name, UNNEST(headcounts) AS headcount
FROM Business
ORDER BY headcount DESC;

array:
SELECT unnest('{1,2}'::INTEGER);

insertUsers:
INSERT INTO Users (name, age)
SELECT * FROM UNNEST(?::TEXT[], ?::INTEGER[])  AS u(name, age); 
// currently must explicitly add alias columns due to wildcard expansion in SqlDelight

updateUsers:
UPDATE Users
SET age = updates.updated_age
FROM UNNEST(?::TEXT[], ?::INTEGER[]) AS updates(name, updated_age)
WHERE Users.name = updates.name;

deleteUsers:
DELETE FROM Users
WHERE (name, age) IN (
  SELECT *
  FROM UNNEST(?::TEXT[], ?::INTEGER[]) AS u(name, age)
);

selectLocations:
SELECT DISTINCT b.*
FROM Business b
JOIN LATERAL UNNEST(b.zipcodes) AS loc(zipcode) ON loc.zipcode ILIKE '%' || :query || '%';

selectBusinessExists:
SELECT *
FROM business
WHERE EXISTS (
    SELECT 1
    FROM unnest(locations) AS loc 
    WHERE lower(loc) LIKE '%' || LOWER(:query) || '%'
);