How to unnest nested repeated records in BigQuery sql

1
votes

I'm trying to conditionally retrieve certain fields in BigQuery standard sql. The problem is the tables I'm working with have multiple levels of nested data. My strategy to use successive CROSS JOIN that reference previous ones to get deeper into nested arrays doesn't seem to be working as expected since I keep getting an error from GCP: Table name "nhht" missing dataset while no default dataset is set in the request. Please see the tables and expected (and explained) output below as well as my attempted query. Thanks in advance:

Attempted query:

select tab2.house_type_id as `house_type_id`,
case when fmd.house_type_metadata_secure.house_type_id=tab2.house_type_id then fh.house_color_data.house_door_color else tab2.house_door_color end as `house_door_color`,
tab2.house_name,
tab2.house_roof_color
from `table1` as tab1,
unnest(tab1.favorite_houses) as fh,
unnest(fh.house_info.house_type_data.house_type_metadata) as fmd,
unnest(tab1.newhouses) as nh,
unnest(tab1.oldhouses) as oh,
unnest(nh.house_type) as nhht,
unnest(oh.house_type) as ohht,
(select * from nhht union all select * from ohht) as comb
left join `table2` as tab2
on comb.house_type_info.house_type_id=tab2.house_type_id

Desired output with explanation:

The logic is to look at all house_type_id's in new_houses and old_houses in table1. (Note that we are guaranteed that the set of all house_type_id's that appear in all new_houses and old_houses arrays across our data contains no duplicates and a house_type_id from old_houses might also appear inside only one favorite_houses list. We are also guaranteed that if a house_type_id appears anywhere in table1 it must appear in table2 but there might be some house_type_id's in table2 that don't appear in table1.)

  • We'll take each of these house_type_id's and select its associated name from table2
  • Select it's house_door_color from table1 within favorite_houses iff this house_type_id appears within favorite_houses and if not get it from table2
  • Select its house_roof_color from table2
{
    {
        "house_type_id": "hid2000", --how we identify data between tables
        "house_name": "oak st.", --should come from table 2
        "house_door_color": "red", --should come from table 1 if house_type_id in favorite houses
        "house_roof_color": "purple", --should come from table 2
    },
    {
        "house_type_id": "hid1000"
        "house_name": "elm st."
        "house_door_color": "black"
        "house_roof_color": "black"
    },
    {
        "house_type_id": "hid3000",
        "house_name": "juniper st.",
        "house_door_color": "grey",
        "house_roof_color": "grey"
    }
}

table1:

{
    "name": "tom",
    "new_houses": [
        {
            "house_name": "elm st.",
            "house_type": [
                {
                    "house_color": "green",
                    "house_type_info": {
                        "house_type_id": "hid1000"
                    }
                }
            ]
        }
    ],
    "old_houses": [
        {
            "house_name": "oak st.",
            "house_type": [
                {
                    "house_color": "blue",
                    "house_type_info": {
                        "house_type_id": "hid2000"
                    }
                }
            ]
        }
    ],
    "favorite_houses": [
        {
            "house_info": {
                "house_name":"oak st.",
                "house_type_data": {
                    "house_type_metadata": [
                        {
                            "house_type_metadata_secure": {
                                "house_type_id": "hid2000"
                            }
                        }
                    ]
                }
            },
            "house_color_data": {
                "house_door_color": "red",
                "house_color": "blue"
            }
        }
    ]
}

table2:

    {
        "house_type_id": "hid2000",
        "house_door_color": "yellow",
        "house_roof_color": "purple",
        "house_name": "oak st."
    },
    {
        "house_type_id": "hid1000",
        "house_door_color": "black",
        "house_roof_color": "black",
        "house_name": "elm st."
    },
    {
        "house_type_id": "hid3000",
        "house_door_color": "grey",
        "house_roof_color": "grey",
        "house_name": "juniper st."
    }
sqlgoogle-bigquery