Arrays of custom type in PostgreSQL

Question

I have a custom type in Postgres:

CREATE TYPE child AS
   (id integer,
    name text,
    surname text,
    age integer);

and table

CREATE TABLE parent
(
  id integer NOT NULL,
  name text NOT NULL,
  surname text NOT NULL,
  age integer NOT NULL,
  childs child[],
  CONSTRAINT parent_pkey PRIMARY KEY (id)
)

and I want to get parents which have a child who has a name = "John"

I tried something like that:

select id, name
  from parent
where 'John' = any  (select (unnest(childs)).name from parent)

but I received all parents. I would be so grateful when someone solve my problem.

Answer 1

You have to somehow correlate first parent and the seconds parent. Either you can use the query by Vao Tsun or try to use correlated query:

select id, name
  from parent as p1
where 'John' = any  (select (unnest(childs)).name from parent as p2 where p1.id = p2.id)

Answer 2

whould this help?..

with un as (
select (unnest(childs)).name, id from parent
) 
select 
  id
, name 
from parent 
join un on un.id = parent.id 
where un.name = 'John'
;

Answer 3

It's easier to see the problem if you alias the tables:

SELECT p1.id, p1.name
FROM parent p1
WHERE 'John' = ANY (SELECT (UNNEST(p2.childs)).name FROM parent p2)

The WHERE clause doesn't even mention p1; it's looking at an independent copy of the parent table. But there's no need to introduce a second copy of the table at all:

SELECT id, name
FROM parent
WHERE 'John' = ANY (SELECT (UNNEST(childs)).name)