mysql starrocks json处理 json遍历数组所有元素

SELECT
     api_id
    ,max(api_path     ) as api_path
    ,max(api_name     ) as api_name
    ,max(api_catalog  ) as api_catalog
    ,max(api_status   ) as api_status
    ,max(case when sql_content    like '%api_flag%' then 1 else 0 end) as sql_api_flag
    ,max(case when sql_content    like '%user_no%'  then 1 else 0 end) as sql_user_no
    ,max(case when instr(sql_content,api_name) > 0 then 1 else 0 end) as sql_api_flag_match
    ,max(case when request_params like '%userNo%'   then 1 else 0 end) as req_user_no
    ,max(case when jt.paramName = 'userNo' then jt.paramPosition end) as paramPosition
    ,group_concat(jt.paramName    )   as paramNameGc
FROM `ds_tools`.`interface_info`  t1
left join JSON_TABLE(
    request_params,
    '$[*]' -- 遍历数组所有元素（*表示所有下标）
    COLUMNS (
        paramName VARCHAR(50) PATH '$.paramName',   -- 提取paramName字段
        paramPosition VARCHAR(50) PATH '$.paramPosition' -- 提取paramPosition字段
    )
) AS jt
     on 1 = 1
WHERE `env` LIKE '%prod%' 
    and api_catalog like '大算报表查询%'
group by api_id
LIMIT 0,1000
;