I have a composite type pin:
CREATE TYPE public.pin AS
(
id uuid,
name text,
);
And a table device:
CREATE TABLE public.devices
(
name text COLLATE pg_catalog."default" NOT NULL,
pins pin[] NOT NULL
)
How do I write a query to select the pin.name from the devices table where pin.id is equal to a known id?
You can use a lateral join for this:
SELECT pin.name
FROM devices, unnest(pins) AS pin -- implicit lateral join
WHERE pin.id = '77068690-787c-431d-9a6f-bd2a069fa5a4' -- random uuid
I managed to solve the problem with this query:
SELECT name FROM (SELECT (UNNEST(pins)::pin).id, (UNNEST(pins)::pin).name FROM public.devices) AS _pins WHERE _pins.id = 'uuid'