Skip to content

Instantly share code, notes, and snippets.

@shotleft

shotleft/bq_nest11.txt

Last active December 27, 2019 05:37
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 shotleft/645c94fd0dab8a7d41ba4bfcf26d40ab to your computer and use it in GitHub Desktop.
Save shotleft/645c94fd0dab8a7d41ba4bfcf26d40ab to your computer and use it in GitHub Desktop.
Download ZIP
bq_nest11
Raw
bq_nest11.txt
SELECT
main.object_id
, title
, artist_display_name
, ARRAY_AGG(lA.description) AS description
FROM `bigquery-public-data.the_met.objects` AS main
INNER JOIN
`bigquery-public-data.the_met.vision_api_data` AS api
ON main.object_id = api.object_id
CROSS JOIN UNNEST(labelAnnotations) lA #notice how UNNEST is placed after the INNER JOIN
WHERE
LOWER(artist_display_name) = 'claude monet'
GROUP BY
1, 2, 3
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment