Using Global Similarity Graph Document Embeddings & BigQuery For "More Like This" Search: Cross Language Search

Earlier today we showed how the new Global Similarity Graph Document Embeddings dataset can be used to take an arbitrary news article and perform a "find more like this" search by computing its cosine similarity to all other articles in the dataset and returning the top matches. In our example the majority of the returned articles were in English, but what if we specifically wanted to see coverage from other languages? We can simply tweak the query below to add "lang != 'ENGLISH'" to our comparison selection to only compare this article against non-English coverage:

SELECT
  c.k1 as query_title,
  c.k2 as match_title,
  SUM(vv1*vv2) / (SQRT(SUM(POW(vv1,2))) * SQRT(SUM(POW(vv2,2)))) AS similarity,
  ANY_VALUE(c.u2) as match_url
FROM
(
  SELECT
    a.key k1, a.val v1, b.key k2, b.val v2, a.url u1, b.url u2
  FROM
  (
    SELECT title key, url url, docembed val FROM `gdelt-bq.gdeltv2.gsg_docembed` WHERE DATE(date) = "2021-07-30" and url='https://www.marketwatch.com/story/cdc-expected-to-release-data-that-informed-change-on-face-mask-policy-says-delta-variant-of-covid-means-war-has-changed-11627655532' LIMIT 1
   ) a
  CROSS JOIN
  (
    SELECT title key, url url, docembed val FROM `gdelt-bq.gdeltv2.gsg_docembed` WHERE DATE(date) = "2021-07-30" and lang != 'ENGLISH'
  ) b
) c
, UNNEST(c.v1) vv1 with offset ind1 JOIN UNNEST(c.v2) vv2 with offset ind2 ON (ind1=ind2)
GROUP BY c.k1, c.k2
ORDER BY similarity DESC
LIMIT 100

That's it! The first 50 results can be seen below: