get_json_object解析json为null

get_json_object(json_string, path)

Extracts json object from a json string based on json path specified, and returns json string of the extracted json object. It will return null if the input json string is invalid. NOTE: The json path can only have the characters [0-9a-z_], i.e., no upper-case or special characters. Also, the keys cannot start with numbers. This is due to restrictions on Hive column names.

特别注意:解析json字符串时,如果遇到\n会出现异常,导致后续获取相关字段为null

regexp_replace(INITIAL_STRING, PATTERN, REPLACEMENT)

Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For examples, regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc.

extension内容

{
    "accompanyEntityList": [
    {
        "id": 100029362,
        "name": "外星人吃茶(厦门)",
        "visitTime": "2023-01-28 14:57",
        "visitId": 514468095
    }],
    "accompanyFeedBack":
    {
        "highlights": "1、主动交互行业信息,赋能意识明显提升;\n2、业务洽谈能力出众,达成活动报名指标;\n3、了解品牌供应链、扩张情况,为后续运营规划奠定基础",
        "remark": "行业信息:因为疫情原因,品牌目前暂未清晰规划后续扩店方向;\n品牌诉求:给予直营店差异化补贴扶持;\n服务情况探查:客情较好,能满足品牌方多方需求",
        "questionAndSuggestList": [
        {
            "question": "专业性仍有待提升,如:对于补贴节奏精准程度的控制;对于KP说法的判断",
            "suggest": "让每一次品牌拜访价值最大化:达成业务结果;梳理运营思路;交互竞对信息"
        }]
    },
    "accompanyEmployName": "韩孟涛",
    "accompanyObjectEmployName": "陈彩云",
    "purpose": "针对疫情放开背景在开工第一天拜访品牌了解后期品牌发展规划"
}
select a.id,

       a.accompany_uid,

       a.accompany_mis,

       a.accompany_object_uid,

       a.accompany_object_mis,

       a.accompany_day,

       get_json_object(a.extension,'$.purpose') purpose,

       get_json_object(a.extension,'$.accompanyFeedBack.highlights') highlights,

       get_json_object(a.extension,'$.accompanyFeedBack.questionAndSuggestList[0].question') question,

       get_json_object(a.extension,'$.accompanyFeedBack.questionAndSuggestList[0].suggest') suggest,

       get_json_object(a.extension,'$.accompanyEntityList[0].visitId') visitId,

       CASE WHEN (get_json_object(a.extension,'$.accompanyFeedBack.remark')!='') then get_json_object(a.extension,'$.accompanyFeedBack.remark')

            else ''

             end remark

  from (

        select id,

               team_id,

               accompany_uid,

               accompany_mis,

               accompany_object_uid,

               accompany_object_mis,

               accompany_day,

               regexp_replace(extension,'\\n',';') as extension

          from origindb.waimai_crm_platf_sales_management__platform_accompany_record

       ) a

 where a.team_id=5006

   and date2datekey(a.accompany_day)between '$begindatekey' and '$enddatekey'

0 0 投票数
Article Rating
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论
0
希望看到您的想法,请您发表评论x