Using multiple nested fields in BigQuery

Question

I have some records that have information about stores. These records have several different nested fields. One of the nested fields is tags and one is employees. I am trying to get a count of the number of stores that have a tag and an employee with a certain name. So I did this:

SELECT count(*)
FROM [stores.stores_844_1]
where tags.tag_name='foo'
and employees.first_name='bar'

Then I get the error:

Error: Cannot query the cross product of repeated fields tags.tag_name and employees.first_name.

I can make it work by changing the query to:

SELECT count(*)
FROM ((flatten([stores.stores_844_1],tags))
where tags.tag_name='foo'
and employees.first_name='bar'

The problem with this is that I am dynamically creating the where clause and so my from clause will have to change depending on what I have in the where. While I could generate some logic in code to figure out what the from clause should be, I was wondering if there is a way to do something like:

SELECT count(*)
FROM [stores.stores_844_1]
where tags.tag_name='foo' WITHIN RECORD
and employees.first_name='bar' WITHIN RECORD

That would not have to flatten the main table? I have tried using an ugly work around like this:

SELECT count(*)
FROM
(SELECT GROUP_CONCAT(CONCAT('>', tags.tag_name,'<')) WITHIN RECORD as f1, GROUP_CONCAT(CONCAT('>',employees.first_name,'<')) WITHIN RECORD as f2
FROM [stores.stores_844_1]
)
where f1 CONTAINS '>foo<'
and f2 CONTAINS '>bar<'

This ugly workaround works how I want it to, but it just seems really hacky and ugly and there must be a better way, right?

Jordan Tigani Jordan Tigani · Accepted Answer · 2013-01-07T21:30:14

You can use WITHIN RECORD to come up with another field that indicates whether the values are present. I'm not sure if this meets your requirements, since you still have to change the FROM clause, but it seems cleaner than what you are currently doing. In other words, try this:

SELECT count(*) FROM (
    SELECT SUM(IF(tags.tag_name='foo', 1, 0)) WITHIN RECORD as has_foo,
           SUM(IF(employees.first_name='bar', 1, 0)) WITHIN RECORD as has_bar,
    FROM  [stores.stores_844_1])
    WHERE has_foo > 0 AND has_bar > 0

Using multiple nested fields in BigQuery

1 Answers