count % nested filter fields

WITH index_fields AS (
  -- Parse each field from the index definitions
  SELECT 
    vectorindexes__name as index_name,
    path as field_path,
    numdimensions,
    type,
    CASE 
      WHEN numdimensions IS NOT NULL THEN 'vector'
      ELSE 'filter'
    END as field_category,
    CASE 
      WHEN numdimensions IS NOT NULL THEN numdimensions * 4
      ELSE NULL
    END as vector_size_bytes,
    -- Identify nested fields
    CASE 
      WHEN numdimensions IS NULL AND path LIKE '%.%' THEN 1
      ELSE 0
    END as is_nested_filter
  FROM awsdatacatalog.cloud_backend_raw.dw__cloud_backend__config_nds_ftsvectorindexconfigs
  CROSS JOIN UNNEST(vectorindexes__fields)
  WHERE vectorindexes__fields IS NOT NULL
),

field_analysis AS (
  SELECT 
    COUNT(DISTINCT CASE WHEN field_category = 'filter' AND is_nested_filter = 1 THEN index_name END) as indexes_with_nested,
    COUNT(DISTINCT index_name) as total_indexes,
    COUNT(CASE WHEN field_category = 'filter' THEN 1 END) as total_filter_fields,
    COUNT(CASE WHEN field_category = 'filter' AND is_nested_filter = 1 THEN 1 END) as nested_filter_fields,
    COUNT(CASE WHEN field_category = 'vector' THEN 1 END) as total_vector_fields
  FROM index_fields
)

SELECT 
  total_indexes,
  indexes_with_nested,
  ROUND(100.0 * indexes_with_nested / total_indexes, 2) as pct_indexes_with_nested,
  total_filter_fields,
  nested_filter_fields,
  ROUND(100.0 * nested_filter_fields / total_filter_fields, 2) as pct_filter_fields_nested,
  total_vector_fields
FROM field_analysis;