Dynamically generate Bigquery case when from JSON

I have a dataset in Bigquery in which one of the fields is a json, and I want to create a table of indicator variables from that json. I wish to use a case when statement like below

SELECT id,
--   SELECT JSON EXTRACT_
--   UNNEST(JSON_EXTRACT(column,
--     "$.rules[0].group")) 
 
  CASE WHEN 
    JSON_EXTRACT_SCALAR(column, '$.rules[0].group') = "interests" OR     
    JSON_EXTRACT_SCALAR(column, '$.rules[1].group') = "interests" OR     
    JSON_EXTRACT_SCALAR(column, '$.rules[2].group') = “interests" OR     
    JSON_EXTRACT_SCALAR(column, '$.rules[3].group') = "interests" OR     
    JSON_EXTRACT_SCALAR(column, '$.rules[4].group') = "interests" OR     
    JSON_EXTRACT_SCALAR(column, '$.rules[5].group') = "interests"
  THEN 1
  ELSE 0
  END indicator_name

FROM
  table

However this query only works on a row in which there are 6 elements in the rules list. The length of the rules list is variable. Also I want to do this for several different ‘groups’ besides interests so that I return a table of ids and several indicator variable columns.

Example of the json for one row:

{"rules":[{"group":"age","key":"age","value":"13-17","operator":"equals"},{"group":"gender","key":"gender","value":"female","operator":"equals"},{"group":"interests","key":"interest","value":"xy_44","operator":"contains"},{"group":"geog","key":"geog","value":"US/","operator":"starts_with"},{"group":"o_version","key":"o_version","value":"ghybu","operator":"starts_with"},{"group":"o_version_min_group","key":"o_version","value":"36573232","operator":"greater_than_or_equals"}],"inventory":{"include":{"bundles":["mpp"]}}

The script running this query uses jinja so that can be used if it helps.

Answer

Consider below approach

SELECT id,
  IF((
    SELECT LOGICAL_OR(JSON_EXTRACT_SCALAR(rule, '$.group') = 'interests')
    FROM UNNEST(JSON_EXTRACT_ARRAY(column, '$.rules')) rule
  ), 1, 0) indicator_name
FROM `project.dataset.table`