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
)
三奇智元机器人科技有限公司公司福利 50人发布