'BigQuery select rows with two (or more / less) matches in a repeated field

I am having a schema that looks like:

[
    {
        "name": "name",
        "type": "STRING",
        "mode": "REQUIRED"
    },
    {
        "name": "frm",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "c",
                "type": "STRING",
                "mode": "REQUIRED"
            },
            {
                "name": "n",
                "type": "STRING",
                "mode": "REQUIRED"
            }
        ]
    },
    {
        "name": "",
        "type": "STRING",
        "mode": "NULLABLE"
    }
]

With a sample record that looks like this:

enter image description here

I am trying to write a query that selects this row when there is a row in frm that matches C = 'X' and another row that has C = 'Z'. Only when both conditions are true, I would love to select the "name" of the parent row. I actually have no clue how I could achieve this. Any suggestions?

E.g. this works, but I am unnesting frm two times, there must a more efficient way I guess.

SELECT name FROM `t2`
WHERE 'X' in UNNEST(frm.c) AND 'Y' in UNNEST(frm.c)


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source