Snowflake flatten Avro File

Question

We recently received a avro file from an external vendor and we loaded this into snowflake. And vendor also provided below query to read columns in snowflake:

SELECT *,COUNT(session_id) AS measure_0
FROM
  TABLE1 session
WHERE
  (EXISTS (
    SELECT 1 AS column_0
    FROM
      UNNEST(session.hits) as hits,
      UNNEST(hits.click) as clicked_text 
    WHERE ((REGEXP_CONTAINS(CAST(clicked_text.text AS STRING), r"(?i)^Activity Report$")))) 
    AND EXISTS (
      SELECT 1 AS column_0
      FROM UNNEST(session.hits) as hits
      WHERE ((REGEXP_CONTAINS(CAST(hits.url.domain AS STRING), r"(?i)^wxb2lb\.ww\.com$|^wxb-tablet\.ww\.com$|^wupb-tablet\.wes\.com$|^wu2\.nun\.com$|^hpos\.ter\.net$|^pos2\.ion\.net$|^hposlb\.ncd\.com$|^ihm-out-retail\.wu\.extranet\.sf\.intra\.laposte\.fr$|^ihm-out-retail\.help\.extranet\.sf\.intra\.laposte\.fr$|^ihm-out-retail-pi\.hp\.wu\.extranet\.sf\.intra\.nposte\.fr$|^ihm-out-retail-rmoa\.hp\.lepo\.extranet\.sf\.intra\.laposte\.fr$")))
    )
    AND NOT EXISTS (SELECT 1 AS column_0 FROM UNNEST(session.hits) as hits WHERE ((REGEXP_CONTAINS(CAST(hits.url.domain AS STRING), r"(?i)uat|rom"))))
    AND session._PARTITIONTIME = '2024-01-28'
  )
LIMIT
  1000

For some reason, UNNEST function is not working here. Can you please suggest whats the mistake here.

ERROR:syntax error line 9 at position 27 unexpected 'as'. (line 26)

If anyone has flattened avro file in tabular data , can you please let me know the steps / documentation.