Return multiple rows for each successful condition from row

Question

I'm trying to return one or many rows for each row input. Here's an example

WITH data as (
  SELECT 1 id, ['main artist', 'supporting artist'] artists
  UNION ALL
  SELECT 2, ['main artist']
  UNION ALL
  SELECT 3, ['supporting artist']
  UNION ALL
  SELECT 4, []
)
SELECT
  id,
  CASE
    WHEN 'main artist' NOT IN UNNEST(artists) THEN "Include main artist"
    WHEN 'supporting artist' NOT IN UNNEST(artists) THEN "Include supporting artist"
    ELSE NULL
  END as error
FROM data

What I want the output to be is

id	error
1	NULL
2	"Include supporting artist"
3	"Include main artist"
4	"Include main artist"
4	"Include supporting artist"

But instead the CASE statement is only returning the value that matches the first condition (as I'd expect with a CASE statement).

I could probably do this by creating a UNION ALL and appending that way but is there a better way that I could do this?

user_5221944 · Answer

Consider below approach

select id, error from (
  select id, array(
    select 'Include ' || missing
    from unnest(['main artist', 'supporting artist']) missing
    left join t.artists artist
    on artist = missing
    where artist is null
  ) errors
  from `project.dataset.table` t
) left join unnest(errors) error
# order by id

If applied to sample data in your question - output is

Return multiple rows for each successful condition from row

1 Answers