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

Time:08-12

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?

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