Search code examples
sqlarraysgoogle-bigquerywhere-clause

Using Array statements in SQL together with Where clause outputs extra rows with null values but it shouldnt

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) enter image description here

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 :

enter image description here

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?

Solution

  • 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)
;