11. 爆炸函数explode结合regexp_replace完成JSON列表解析

工作中，我们经常会遇到要离线分析某些数据的场景。

比如下面部分数据，表示的就是机构(mcn_id)下每个作者(object_id)的任务考核情况（stats_next_target),这里就演示了一个机构，四个作者，实际情况可能是上万机构，上百万的作者。

现在有一个诉求是需要统计每个机构下仅差某一个指标没有完成的作者数量，按机构和指标统计。

SELECT 
    id,mcn_id,object_id,stats_next_target
FROM  webcast.mcn_strategy_process_distinct
where date = '${date}' -- T-1分区
and mcn_id = 2431955970

因为可能要考核作者多个指标，所以指标考核情况是一个列表，样例如下，其中status表示的是当前指标是否完成。

[{
	"cond_alias": "粉丝量",
	"cur_val": "34139",
	"target_val": "50000",
	"status": false
}, {
	"cond_alias": "精选内容占比",
	"cur_val": "0.3000",
	"target_val": "0.2",
	"status": true
}, {
	"cond_alias": "完成指定签约",
	"cur_val": "未完成",
	"target_val": "已完成",
	"status": false
}]

由于是一个列表，所以我们想到使用爆炸函数将每个指标都单独拿出来作为一个新列的值

以下SQL就是一个通用的思路

• 去掉列表外围多余的中括号，使用替换函数
• 将原来的每个json元素使用一个json中没有的符号替换，比如将},{替换为}||{（因为爆炸函数后面要用split将各元素进行切割作为一行的列值，原有的是“,”,json元素中基本都有这个符号，所以不适合作为分割符，因此替换为“||”,如果json中本身就可能出现“||”，那我们就需要考虑其他符号了，比如&&等，总之只要是原json元素中不会出现的符号就好）
• 使用split按我们上面约定的分隔符进行分割，用于爆炸函数上

SELECT 
    *
 FROM  webcast.mcn_strategy_process_distinct
  LATERAL VIEW explode(split(
  regexp_replace(
      regexp_replace(stats_next_target, '^\\[|\\]$', ''),
      '\\},\\{', '}||{'
  ), '\\|\\|')) t as json_element
  where date = '${date}' -- T-1分区

可见原本的4条记录变为了12条记录，因为三个指标分别作为新列值产生了对应的三行，如下：

有了以上知识后，原诉求就比较好实现了

-- 首先将已有的记录进行爆炸函数拆开处理
  with t1 as (
    SELECT 
	    id,
	    mcn_id,
	    object_id,
	    json_element
    FROM  webcast.mcn_strategy_process_distinct
    LATERAL VIEW explode(split(
    regexp_replace(
        regexp_replace(stats_next_target, '^\\[|\\]$', ''),
        '\\},\\{', '}||{'
    ), '\\|\\|')) t as json_element
    where date = '${date}' -- T-1分区
  ),

-- 按作者维度进行聚合统计，统计每条任务记录还差几个指标能到下一阶段
  t2 as (
    SELECT 
    id,
     object_id,
     mcn_id,
     SUM(CASE WHEN get_json_object(json_element, '$.status') = 'false' THEN 1 ELSE 0 END) as false_count
    FROM t1
   group by id,object_id,mcn_id
  ),

  -- 找出仅查一个指标便可完成当前阶段的所有作者
  t3 as (
    select 
    id,
    object_id,
    mcn_id
    from t2
    where false_count = 1
  ),

  -- 找出这些作者具体差哪个指标没有完成
  t4 as (
    select 
        t1.mcn_id,
        t1.object_id,
    get_json_object(t1.json_element, '$.cond_alias') as cond_alias
    from t1
    join t3  -- 内连接，即只保留仅差一个指标便可到下一阶段的任务记录
    on t1.id = t3.id and t1.object_id = t3.object_id and t1.mcn_id = t3.mcn_id
    -- 仅需要当前指标是未完成的
    where get_json_object(t1.json_element, '$.status') = 'false' 

  )

  -- 最后聚合每个指标仅差当前指标便可完成当前阶段的人数
  select 
   t4.mcn_id as mcn_id,
   t4.cond_alias as cond_alias,
   count(cond_alias) as cnt
from t4
group by mcn_id,cond_alias
order by mcn_id