How to search through an array of complex types?

Question

PostgreSQL

Hello.
I have a complex type user_rating :
userId : int
isExpert : bool
rating : integer
There is a table tbl_ratings
id : int
ratings : user_rating[]
As you can see, the ratings field is an array of the user_rating complex type.
All rows must be selected from this table, the ratings field (array) contains at least one element with isExpert = true.
I found a solution how to make conditions for a specific element:
SELECT * FROM tbl_ratings WHERE (ratings[1]."isExpert") = true
But how can I make it search the entire array? That is to remove this index 1?
Thanks in advance )

Answer 1

In general, I did not find anything better than writing a simple function:

CREATE OR REPLACE FUNCTION acc_has_expert_rating(ratings acc_rating[])
    RETURNS bool AS
$$
DECLARE
    rating acc_rating; 
BEGIN

  FOREACH rating IN ARRAY ratings
  LOOP 
      IF rating."isExpert" THEN 
        RETURN TRUE;
      END IF;
  END LOOP;

  RETURN false;
END;
$$
LANGUAGE 'plpgsql';

Answer 2

Found a possible solution, try:

SELECT * FROM tbl_ratings WHERE id IN 
    (SELECT id FROM 
        (SELECT id, unnest(ratings) AS list_item FROM tbl_ratings) explode
        WHERE (explode.list_item).isExpert = true)

Answer 3

But how to make it search the entire array?

ANY()? ALL()?