I am trying to join two tables on arrays that could change in size. I have tried to illustrate it in the example below. One of the tables is called zoo that has different cages and different animals in these cages.

Now the zoo is going to get more animals and to know which animal is going to go in which cage we have the guidelines table. It has a column called if_cage_has_all_these_animals which means as the name suggests the cage has to have all the animals specified in this array to get animals specified in the column more_animals_to_be_added.

In the result after joining these two tables i want the cage_id and all the animals that are in each cage.

The zoo table is produced as
SELECT 1 as cage_number, ['cats','parrots', 'dogs','ants'] AS animals union all select 2 as cage_number, ['bears', 'jaguars', 'lions'] AS animals and looks like this:
[zoo][1]

and the guidelines table can be produced by SELECT 1 as task_id, ['cats', 'dogs'] AS if_cage_has_all_these_animals, ['rats', 'geese'] AS more_animals_to_be_added union all select 2 as id, ['bears', 'jaguars', 'lions'] AS if_cage_has_all_these_animals, ['falcon'] AS more_animals_to_be_added union all select 3 as id, ['parrots'] AS if_cage_has_all_these_animals, ['crow'] AS more_animals_to_be_added and looks like
[guidelines][2]

The result table will look like this:
[result after addition of new animals][3]
For cage 1 both task_id 1 and 3 were used since it had cats and dogs (task_id 1) and parrots (task_id 3)

| Row | cage_number | animals
| —-| ———— |
| 1 | 1 |cats
| | |parrots
| | |dogs
| | |ants
| | |rats
| | |geese
| | |crow
| 2 | 2 |bears
| | |jaguars
| | |lions
| | |falcon
[1]: https://i.sstatic.net/tCJncijy.png
[2]: https://i.sstatic.net/tiav3Uyf.png
[3]: https://i.sstatic.net/gvUB8tIz.png