I started experimenting with BigQuery and I encountered SQL syntaxes such as array and unnest. I created a nested table (is this the correct name? ) and wrote an example query(below)
I wanted to get the department of 'enes' so I wrote this query:
SELECT
dept,
ARRAY(SELECT employeeName FROM UNNEST(name) AS employeeName
WHERE ( (employeeName LIKE 'enes') and
(employeeName is not null))) AS EmpNameDisplay
FROM EmployeeTable
however results looks like this :
Why it brings the second row? What if I had million departments and it will try to bring them all?
I reformed the table in a way it doesnt include nesting tables. And when I use where statement to choose the department of 'enes' it only showed only one result which is correct. Why this nesting causes such abnormaly?
CodePudding user response:
I suppose you need something like this:
with EmployeeTable as
(
select 'Data Engineer' as dept, ['enes','emre','john'] as name
union all select 'Manager' as dept, ['machiavelli','john'] as name
)
select t1.dept,
employeeName
from EmployeeTable t1,
UNNEST(t1.name) as employeeName
where employeeName = 'enes'
;
If you don't need employee name in the result list (department name only), you can use this:
with EmployeeTable as
(
select 'Data Engineer' as dept, ['enes','emre','john'] as name
union all select 'Manager' as dept, ['machiavelli','john'] as name
)
select t1.dept
from EmployeeTable t1
where 'enes' in UNNEST(t1.name)
;