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'