title: Using the results of a STRING_AGG function with the IN operator in a WHERE clause
body: <p>I have column <code>children_ids</code> which contain PKs from a STRING_AGG function. I am trying to use this column within a <code>WHERE</code> clause with the <code>IN</code> operator to return the <code>total_pets</code> but it doesn't work. If I copy and paste the values directly into the <code>IN</code> operator the query returns the correct info, otherwise no reuslts are found.</p>
  
  <p>Here are my data sets:</p>
  
  <pre><code>Parents
  =======
  id  parent_name
  ----------------
  1   Bob and Mary
  2   Mick and Jo
  
  Children
  ========
  id  child_name  parent_id
  -------------------------
  1   Eddie       1
  2   Frankie     1
  3   Robbie      1
  4   Duncan      2
  5   Rick        2
  6   Jen         2
  
  Childrens Pets
  ===============
  id  pet_name  child_id
  -------------------------
  1   Puppy     1
  2   Piggy     2
  3   Monkey    3
  4   Lamb      4
  5   Tiger     5
  6   Bear      6
  7   Zebra     6
  
  Expected Output
  ===============
  parent_id  children_ids  total_pets
  -----------------------------------
  1          1,2,3         3
  2          4,5,6         4
  
  Current [undesired] Output
  ==========================
  parent_id  children_ids  total_pets
  -----------------------------------
  1          1,2,3         0
  2          4,5,6         0
  </code></pre>
  
  <p>here is the standard sql to test for yourself</p>
  
  <pre><code># setup data with standardSQL
  WITH `parents` AS (
    SELECT 1 id, 'Bob and Mary' parent_names UNION ALL
    SELECT 2, 'Mick and Jo'
  ),
  `children` AS (
    SELECT 1 id, 'Eddie' child_name, 1 parent_id UNION ALL     
    SELECT 2, 'Frankie', 1 UNION ALL     
    SELECT 3, 'Robbie', 1 UNION ALL     
    SELECT 4, 'Duncan', 2 UNION ALL
    SELECT 5, 'Rick', 2 UNION ALL
    SELECT 6, 'Jen', 2
  ),
  `childrens_pets` AS (
    SELECT 1 id, 'Puppy' pet_name, 1 child_id UNION ALL     
    SELECT 2, 'Piggy', 2 UNION ALL     
    SELECT 3, 'Monkey', 3 UNION ALL     
    SELECT 4, 'Lamb', 4 UNION ALL
    SELECT 5, 'Tiger', 5 UNION ALL
    SELECT 6, 'Bear', 6 UNION ALL 
    SELECT 7, 'Zebra', 6
  )
  </code></pre>
  
  <p>And the query:</p>
  
  <pre><code>#standardSQL
  select
    parent_id
    , children_ids
  
  -- !!! This keeps returning 0 instead of the total pets for each parent based on their children
    , (
      select count(p1.id)
      from childrens_pets p1
      where cast(p1.child_id as string) in (children_ids)
    ) as total_pets
  from
  (
    SELECT 
      p.id as parent_id
      , (
        select string_agg(cast(c1.id as string))
        from children as c1
        where c1.parent_id = p.id
      ) as children_ids
  
    FROM parents as p
      join children as c
        on p.id = c.parent_id
      join childrens_pets as cp
        on cp.child_id = c.id
  )
  GROUP BY
    parent_id
    , children_ids 
  </code></pre>