Skip to content

Instantly share code, notes, and snippets.

@hectorperez

hectorperez/gist:537d3f49c8821c0cf7ed9a027277a97a

Created November 24, 2016 18:30
Show Gist options
  • Save hectorperez/537d3f49c8821c0cf7ed9a027277a97a to your computer and use it in GitHub Desktop.
Save hectorperez/537d3f49c8821c0cf7ed9a027277a97a 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.P108[0].mainsnak.datavalue.value.numeric-id’) award1,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[1].mainsnak.datavalue.value.numeric-id’) award2,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[2].mainsnak.datavalue.value.numeric-id’) award3,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[3].mainsnak.datavalue.value.numeric-id’) award4,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[4].mainsnak.datavalue.value.numeric-id’) award5,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[5].mainsnak.datavalue.value.numeric-id’) award6,
JSON_EXTRACT_SCALAR(item, ‘$.claims.P108[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=182 AND (w.award1=”49108" OR w.award2=”49108" OR w.award3=”49108" OR w.award4=”49108" OR w.award5=”49108" OR w.award6=”49108" OR w.award7=”49108")
limit 100
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment