CK -sql

json字符串解析

select
  DISTINCT schoolId,
  schoolIdCN,
  areaCode,
  classCode,
  name,
  email,
  r_time,
  log_time,
  replaceAll(
	visitParamExtractRaw(click_list, 'material_id'),
	'"',
	''
  ) material_id,
  replaceAll(
	visitParamExtractRaw(click_list, 'material_name'),
	'"',
	''
  ) material_name,
  visitParamExtractRaw(click_list, 'pageNum') pageNum,
  replaceAll(visitParamExtractRaw(click_list, 'type'), '"', '') material_type,
  visitParamExtractRaw(click_list, 'clickTime') clickTime,
  clickType
from
  iteach_front_db.ods_web_log
where
  app_environment = 'online'
  and toDate (r_time) > '2021-09-20'
  and name <> ''
  and clickType in ('44', '45', '46', '47')