Unescaping Article Titles In The GKG 2.0

Since September 2019 the GKG 2.0 has included the title of each article in a "<PAGE_TITLE></PAGE_TITLE>" block in the "Extras" field. In keeping with the GKG 2.0's legacy ASCII format, titles that include non-ASCII characters are HTML-escaped. Thus, you will see strings like "&#x627;&#x6CC;&#x645; &#x6A9;&#x6CC;&#x648; &#x627;&#x6CC;&#x645; &#x6A9;&#x648;" in place of "ایم کیو ایم کو" since all non-ASCII characters are escaped.

This can present a challenge when working with the GKG 2.0 in BigQuery, since the platform does not provide a native HTML entity unescape function. Thankfully, BigQuery's UDF functionality allows us to trivially add this capability.

To extract the title for post-September 2019 GKG 2.0 records, simply use "REGEXP_EXTRACT(Extras, r'<PAGE_TITLE>(.*?)<\/PAGE_TITLE>')" and to escape them using our UDF, simply wrap it in a call to "titleunescape" and copy-paste the UDF to the top of the query as follows:

CREATE TEMPORARY FUNCTION titleunescape(title STRING) RETURNS STRING LANGUAGE js AS '''
  return title.replace(/(&#x)([a-zA-Z0-9]+)(;)/gu, function (whole, capture1, capture2) {
    return String.fromCodePoint(parseInt(capture2, 16))
  });
''';

SELECT DocumentIdentifier, REGEXP_EXTRACT(Extras, r'<PAGE_TITLE>(.*?)<\/PAGE_TITLE>') title, titleunescape( REGEXP_EXTRACT(Extras, r'<PAGE_TITLE>(.*?)<\/PAGE_TITLE>') ) titleunescaped, REGEXP_EXTRACT(TranslationInfo, r'srclc:(.*?);') lang FROM `gdelt-bq.gdeltv2.gkg_partitioned` WHERE DATE(_PARTITIONTIME) = "2021-08-04" and (V2Themes like '%VACCIN%' OR V2Themes like '%IMMUNIZATION%') and Extras like '%&#x%'

You can see the results below, with the "title" column showing the raw HTML-escaped title as it appears in the GKG 2.0 record and the "titleunescaped" column containing the unescaped original Unicode version.

Once again, the power of UDFs makes tasks trivial in BigQuery!