Skip to content

Instantly share code, notes, and snippets.

@hectorperez

hectorperez/gist:b19f0ce6b324670a6e6371529679837f

Created November 24, 2016 18:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save hectorperez/b19f0ce6b324670a6e6371529679837f to your computer and use it in GitHub Desktop.
Save hectorperez/b19f0ce6b324670a6e6371529679837f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment