gistfile1.txt

select w.name, a.extent from agreelist.agreements a
join agreelist.individuals i on i.id=a.individual_id
join(
 SELECT JSON_EXTRACT_SCALAR(item, ‘$.id’) id,
 JSON_EXTRACT_SCALAR(item, ‘$.sitelinks.enwiki.title’) name,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[0].mainsnak.datavalue.value.numeric-id’) award1,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[1].mainsnak.datavalue.value.numeric-id’) award2,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[2].mainsnak.datavalue.value.numeric-id’) award3,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[3].mainsnak.datavalue.value.numeric-id’) award4,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[4].mainsnak.datavalue.value.numeric-id’) award5,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[5].mainsnak.datavalue.value.numeric-id’) award6,
 JSON_EXTRACT_SCALAR(item, ‘$.claims.P166[6].mainsnak.datavalue.value.numeric-id’) award7
 FROM [fh-bigquery:wikidata.latest_en_v1]
) w ON i.wikidata_id=w.id
WHERE a.statement_id=7 AND (w.award1=”47170" OR w.award2=”47170" OR w.award3=”47170" OR w.award4=”47170" OR w.award5=”47170" OR w.award6=”47170" OR w.award7=”47170")
limit 100