SQL生成JSON

1.拼接json背景

作为数仓同学，除了给业务方搭建看板、建设宽表等，同时也会给后端推送数据，应用在产品展示中，一般在推数之前后端同学会提供MQ Topic及Address，用于数据传输，数据传输可通过数据集成工具（Sea Tunnel），或Flink进行消息推送，因此需要数仓同学将数据转化为JSON格式。

2.消息任务开发流程

因此开发任务为3个

任务1：开发出业务需要的数据可以是明细、也可以是指标（逻辑由产品+业务出），任务命名为dwd_xxxx_di/df or ads_xxxx

任务2：将数据转化成json格式（就是接下来要说的），任务命名为dwd_xxxx_json_di/df or ads_xxxx_json

建议大家在做json表时候留个key，要不后续定位json数据时候，你还需要get_json_object，同时在开发时候注意json内容和后端要求的映射。

任务3：建设数据集成 or Flink任务

dev环境推送后和后端及测试确认推数情况（可能由于json格式不对（符号多/少）导致message被拦截推送数据失败）

如果是纯实时可通过Flink SQL操作，同理也是3个任务，或者一个任务中通过多source完成，具体时效看业务要求（30min/1h/t+1都有可能）

hive2mq/kafka

3.样例数据

这里还是以一个社区动态数据为例子,我们要把用户在社区发布数据展示在平台中，供内容审核人员去审核。

原表结构数据为

with tt1 as (
select '语兴好物好鞋分享' as  tag 
      ,'Asics JOG 100 2 轻便透气入门级专业跑步鞋 黑武士 男女同款' as spu_list
      ,'' as circle
      ,'动态图文' as content_type
      ,'APP发布' as source
      ,'[]' as ocr_result
      ,'{}' as video_result
)


select tag
      ,spu_list
      ,circle
      ,content_type
      ,source
      ,ocr_result
      ,video_result
from  tt1

生成json目标为：

{
    "tag": "语兴好物好鞋分享",
    "spuList": "Asics JOG 100 2 轻便透气入门级专业跑步鞋 黑武士 男女同款 ",
    "circle": "",
    "contentType": "动态图文",
    "source": "APP发布",
    "ocrResult": "[]",
    "videoResult": "{}"
}

4.to_json+map组合拼接（最快最直接办法）

with tt1 as (
select '语兴好物好鞋分享' as  tag 
      ,'Asics JOG 100 2 轻便透气入门级专业跑步鞋 黑武士 男女同款' as spu_list
      ,'' as circle
      ,'动态图文' as content_type
      ,'APP发布' as source
      ,'[]' as ocr_result
      ,'{}' as video_result
)


select to_json(
                map('tag',tag,
                    'spuList',spu_list,
                    'circle',circle,
                    'contentType',content_type,
                    'source',source,
                    'ocrResult',ocr_result,
                    'videoResult',video_result
                )
        )
from tt1

通过json.cn或其他json查看工具可查看json拼接是否准确

5.concat（纯手拼，比较费力但结构完全可按照自己方式来）

原数据如下，还是社区数据

with tt2 as (
select 242165710 as  content_id
      ,'xhs' as content_source
      ,'图文' as content_type
      ,'时尚简约长裤，轻松驾驭任何风格👖' as content_title
      ,'时尚简约长裤，轻松驾驭任何风格👖。优质面料，裁剪利落，无论是搭配牛仔裤还是衬衫，都不失格调，让你从人群中脱颖而出' as content
      ,'xxxxx' as content_url
      ,4353412 as publish_user_id
      ,'xx' as tag_list
      ,'xxx' as tag_name_list
      ,'425436' as spu_id_list
      ,'xx牛仔裤' as spu_name_list
      ,'xxx' as spu_url
      ,'2024-11-23 01:20:34' as publish_time
)

目标是拼接如下内容


with tt2 as (
select 242165710 as  content_id
      ,'xhs' as content_source
      ,'图文' as content_type
      ,'时尚简约长裤，轻松驾驭任何风格👖' as content_title
      ,'时尚简约长裤，轻松驾驭任何风格👖。优质面料，裁剪利落，无论是搭配牛仔裤还是衬衫，都不失格调，让你从人群中脱颖而出' as content
      ,'xxxxx' as content_url
      ,4353412 as publish_user_id
      ,'xx' as tag_list
      ,'xxx' as tag_name_list
      ,'425436' as spu_id_list
      ,'xx牛仔裤' as spu_name_list
      ,'xxx' as spu_url
      ,'2024-11-23 01:20:34' as publish_time
)
, tt3 as (
select content_id
      ,content_source
      ,content_type
      ,content_title
      ,content
      ,content_url
      ,publish_user_id
      ,tag_name_list
      ,spu_id_list
      ,spu_name_list
      ,spu_url
      ,image_url
      ,publish_time
      ,concat('话题id:',tag_list,'<br>','话题name:',tag_name_list)  as extra
      ,to_json(
                map(
                     'spuIdList:',spu_id_list,
                     'spuNameList:',spu_name_list,
                     'spuUrl:',spu_url
                )
        )   as extra2
from tt2
)

select  cast(t0.content_id as string) as content_id
       ,concat(concat('{'
                   ,'\"contentId\":\"',md5(concat('社区数据',publish_user_id,cast(content_id as string))),'\",'
                   ,'\"contentId\":\"',content_id,'\",'
                   ,'\"contentSource\":\"',content_source,'\",'
                   ,'\"contentType\":\"',content_type,'\",'
                   ,'\"contentTitle\":\"',content_title,'\",'
                   ,'\"content\":\"',content,'\",'
                   ,'\"contentExtra\":"',GET_JSON_OBJECT(TO_JSON(map('extra',extra)),'$.extra'),'",'   
                   ,'\"spuExtra\":',extra2,','   
                   ,'\"contentUrl\":\"',content_url,'\",' 
                   ,'\"publishTime\":\"',UNIX_TIMESTAMP(publish_time),'\",' 
                   ,'\"publishUserId\":\"',cast(publish_user_id as string),'\",'  
                   ,'}'  
                )  
       )
FROM  tt3

这里再提一下为什么选择flink去传而不是数据同步工具，因为数据同步工具会给json内部list或json（json套json）加引号给引起来，因此最后传给后端可能就不是一个json体。

6.GPT实现

费时费力就面向GPT编程（GPT 4o、豆包、文心、kimi都行，没有广告！！！），prompt如下：

使用hive sql 将如下内容拼接成一个字段的json体，with tt2 as (
select 242165710 as  content_id
      ,'xhs' as content_source
      ,'图文' as content_type
      ,'时尚简约长裤，轻松驾驭任何风格👖' as content_title
      ,'时尚简约长裤，轻松驾驭任何风格👖。优质面料，裁剪利落，无论是搭配牛仔裤还是衬衫，都不失格调，让你从人群中脱颖而出' as content
      ,'xxxxx' as content_url
      ,4353412 as publish_user_id
      ,'[xx,xxx,xx]' as tag_name_list
      ,'[425436]' as spu_id_list
      ,'[xx牛仔裤]' as spu_name_list
      ,'[xxx]' as spu_url
      ,'[xxxx,xxx]' as image_url
)