String Functions On Unnested Arrays In BigQuery SQL
I'm trying to find all instances of rows where one of the array items contains a value, ignoring the case in Google BigQuery.
I'm able to do this:
SELECT COUNT(*) FROM `my_bq_project.my_bq_dataset.my_table` WHERE ( 'AAA' IN UNNEST(array_1.array_2.some_text) )
This works well for finding
array_1.array_2.some_text. But I would like to also find
'Aaa', and so forth.
But this does not work:
SELECT COUNT(*) FROM `my_bq_project.my_bq_dataset.my_table` WHERE ( 'AAA' IN UNNEST(LOWER(array_1.array_2.some_text)) )
And I would like to refrain from duplicating the where statement (adding where conditions for
Is there a way to apply string functions like
LOWER on struct fields that are in arrays in BigQuery SQL?
Try below approach
SELECT COUNT(*) FROM `my_bq_project.my_bq_dataset.my_table` WHERE LOWER('AAA') IN ( SELECT LOWER(some_text) FROM UNNEST(array_1) a, UNNEST(a.array_2) )
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key