I have the following table in Snowflake

  categories VARIANT DEFAULT NULL,
  types VARIANT DEFAULT NULL,
  id VARCHAR(255)

categories = [{"name", "id"}]
types = [{"name", "id"}]

I want to select all the id’s where they have type.name=x and category.name=y.
I tried to use subquery but its not working.

    SELECT 
      id
    FROM 
        TEST.TEST.TEST
    WHERE
        EXISTS (
            SELECT 
                VALUE:name::string
            FROM 
                TABLE(FLATTEN(input => categories)) 
            WHERE
                VALUE:name::string  = 'x'
        )
  AND EXISTS (
            SELECT 
                VALUE:name::string
            FROM 
                TABLE(FLATTEN(input => types)) 
            WHERE
                VALUE:name::string  = 'Y'
        )

I get the following error:

Unsupported subquery type cannot be evaluated