Regex for bigquery to return all paths after /api

user_11872451 0
Viewed 82

I am using dataset of github to extract all paths after /api/* and for that I used this below-mentioned query. However, the results are not what I expected it to be. If the regex is wrong can someone please correct it?

Expected results:

  • /api/v1/user

  • /api/anything/anything

What actually returns:

  • Frameworks/TwitterKit.framework/Resources
  • doc/source/README.rst
  *
FROM 
   `bigquery-public-data.github_repos.files`
WHERE
(REGEXP_CONTAINS(path,r'(s|^.*/api/([^/]*)(?:/.*)?$|$1|)'))
LIMIT
100```
google-bigquery github regex
2 Answers

You are using a Perl substitution command in the regex pattern. Look:

s|^.*/api/([^/]*)(?:/.*)?$|$1 |
||                        |RHS
||___pattern______________|
|___ action

where RHS (right-hand side) is the replacement.

You only need to use a pattern in BigQuery. To match your desired strings, you may use

^/api/[^/]*(?:/.*)?$

See the RE2 regex demo.

SELECT * FROM `bigquery-public-data.github_repos.files`
  WHERE REGEXP_CONTAINS(path,r'^/api/[^/]*(?:/.*)?$')
    LIMIT 100
user_38329700

If the regex is wrong can someone please correct it?

#standardSQL
SELECT *
FROM `bigquery-public-data.github_repos.files`
WHERE REGEXP_CONTAINS(path, r'/api/.*')
LIMIT 100

Meantime, note: title of your question is not consistent with question body - REGEXP_CONTAINS in WHERE clause just allows you to return all rows with searched pattern in path - but does not extract the pattern.

To extract pattern - you need to use REGEXP_EXTRACT(path, r'/api/.*') in SELECT statement.

user_52219440
Related