 <p>Mikhail answer is good! Note that there is some adjustments needed, that were performed with SPLIT and REGEXP_EXTRACT, as the JSON_EXTRACT functions don't handle arrays well enough.</p> <p>An alternative, in case you want to work with a BigQuery JavaScript UDF:</p> <pre><code>SELECT userid, word, COUNT(*) c FROM ( SELECT * FROM js( // I wish you had given me a sample table instead when asking the question (SELECT * FROM (SELECT 123 AS id, '{"totalItems":2,"items":[{"word":"drink"},{"word":"food"}]}' AS items), (SELECT 456 AS id, '{"totalItems":3,"items":[{"word":"food"},{"word":"dog"},{"word":"drink"}]}' AS items), (SELECT 123 AS id, '{"totalItems":1,"items":[{"word":"drink"}]}' AS items) ), // Input columns. id, items, // Output schema. "[{name: 'word', type:'string'}, {name: 'userid', type:'integer'}]", // The function. "function(r, emit) { x=JSON.parse(r.items) x.items.forEach(function(entry) { emit({word:entry.word, userid:r.id}); }); }" ) ) GROUP BY 1,2 </code></pre>