CODEWITHSUNDEEP
google-bigquery
Kazuaki Suzuki
Kazuaki Suzuki

Reputation: 1075

How to count a phrase in Big Query view

I have fir-ui-af***:firestore_export dataset in my BigQuery project.

In this dataset, I have speech_raw_changelog table and speech_raw_latest view.

This is schema of speech_raw_latest view. enter image description here

In data field which I marked with a red pen, there is JSON data. This is an example of field and value in data. enter image description here

I would like to count the number of ******[email protected] in email field in this speech_raw_latest view.

I searched the Internet and found that I should use COUNT function, but I don't know how to use it to solve my problem.

Could you give me any advice, please?

Upvotes: 1

Views: 41

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is for BigQuery Standard SQL

SELECT COUNTIF(JSON_EXTRACT_SCALAR(data, '$.email') = 'ka@gmail')
FROM `project.dataset.speech_raw_latest`

Upvotes: 1

Related Questions