Query BigQuery nested/repeated fields
<p>I am new to BigQuery (and sql) and am trying to setup a table where each row contains a <code>message</code> column (string) and <code>metadata</code> column (repeated).  I want to use the metadata column so that I can query for messages that match certain metadata parameters.</p>

<p>For example, my message string is:</p>

<p><code>Hi honey, I'm home!</code></p>

<p>and the metadata column is arbitrary key/value pairs that describe the message: </p>

<p><code>{"category": "personal",
 "message_type": "salutation"}</code></p>

<p>This would allow me to ask bigquery to: </p>

<p><em>return me all messages which have a <code>category</code> of <code>personal</code></em> (or <code>message_type</code> of <code>personal</code>, etc)</p>

<p>To reiterate, the metadata keys will potentially be different for every message row that is inserted (e.g. a new row may have a metadata key of <code>favorite_color</code> instead of <code>category</code>).</p>

<p>Here is the schema that I came up with in order to support the metadata flexibility that I desire:</p>

<pre><code>[
{"name": "message", "type": "string", "mode": "required"}
{"name": "metadata", "type": "record", "mode": "repeated", "fields": [ {"name": "key", 
                                                                        "type": "string", 
                                                                        "mode": "required"},
                                                                       {"name": "value", 
                                                                        "type": "string", 
                                                                        "mode": "required"}]}
]
</code></pre>

<p>The schema expects that every metadata entry/row contain a column called <code>key</code> and column called <code>value</code>.  It seems to work fine.  </p>

<p>Interestingly, Bigquery represents my message row as two rows (which I guess is called "flattening"?):</p>

<pre><code>message                 | metadata_key    |    metadata_value
------------------------------------------------------------
Hi honey, I'm home!       category             personal
Hi honey, I'm home!       message_type         salutation
</code></pre>

<p>I figured out how to query against a single metadata value such as <code>category</code>=<code>personal</code>:</p>

<pre><code>SELECT * FROM [table.test] 
WHERE
metadata.key="category" AND metadata.value="personal"
</code></pre>

<p>I get a single row back:</p>

<pre><code>message                 | metadata_key    |    metadata_value
------------------------------------------------------------
Hi honey, I'm home!       category             personal
</code></pre>

<p>This is great!  However, I don't know how to construct a more complex query that matches against multiple metadata parameters, such as:</p>

<p><em>Fetch me all messages which have a <code>category</code> of <code>personal</code> and <code>message_type</code> of <code>salutation</code></em></p>

<p>This following query doesn't return anything:</p>

<pre><code>SELECT * FROM [table.test] 
WHERE
metadata.key="category" AND metadata.value="personal" AND
metadata.key="message_type" AND metadata.value="salutation" 
</code></pre>

<p>I assume this is because bigquery is breaking down my single message row into two rows (one for each nested metadata row). This obviously means that my query will never be able to match a single row because I am trying to match against two different metadata rows (i.e. I'm asking for metadata.key to be equal to two different values at the same time, and metadata.value to be equal to different values at the same time). So I'm somewhat stumped as to how to construct my query appropriately.</p>

<p>Ideally I would like BigQuery to return a <strong>single</strong> row (the original example row that I started with), since it satisfies the two metadata requirements of my query. </p>

<p>I assume I need some combination of GROUP BY, FLATTEN, WITHIN, unflatten, etc. The combination of possibilities/structure are somewhat daunting for this sql/bigquery novice.  Any help appreciated :)</p>