大数据-玩转数据-mysql之json格式数据提取

最新推荐文章于 2024-02-02 14:43:54 发布

人猿宇宙

最新推荐文章于 2024-02-02 14:43:54 发布

阅读量385

点赞数

文章标签： mysql json 数据库

本文链接：https://blog.csdn.net/s_unbo/article/details/129698200

版权

一、JSON 格式样式

{
    "isDLine":"Y",
    "corporationInfo":{
        "customContact":"李啊",
        "customLevel":"A3",
        "customTechContact":"李啊",
        "customNo":"108qygyy",
        "customContactPhone":"15296699751",
        "customServiceLevel":"高级",
        "customTechContactPhone":"15196614751@163.com",
        "customManager":"啊吗",
        "customManagerPhone":"15196614751",
        "customTrade":"互联网",
        "customName":"看看的01",
        "customAdd":""
    },
    "productInfos":[
        {
            "productType":"0",
            "productName":"云主机",
            "cloudType":"0",
            "belongCloud":"爱啊节点",
            "interested":"否",
            "productCode":{
                "ipType":"IPV4",
                "isoId":"linux-Centos7.1",
                "unsubscribeDes":"",
                "vpcIp":"",
                "cnId":"2023031501",
                "openPorts":"22",
                "cpu":2,
                "memory":4,
                "sysDiskType":"SSD",
                "productType":"0",
                "bandwidth":"",
                "privateIp":"",
                "mountDiskType":"SSD",
                "sysDisk":40,
                "customIp":"",
                "mountDisk":40,
                "isCmNet":"N",
                "vmName":"lky是8",
                "productName":"云主机",
                "wkSum":"1",
                "zjType":"虚拟机",
                "gpu":""
            }
        }
    ],
    "operateSubType":"",
    "operateType":"0",
    "orderInfo":{
        "createTime":"20230315",
        "subCorp":"成都",
        "salesManagerEmail":"11111113@139.com",
        "salesManagerPhone":"1511111113",
        "endTime":"2099-12-31 00:00:00",
        "salesManager":"张吧",
        "productOrderNumber":2023031501,
        "productArrangementNumber":"1053567456",
        "corpName":"张吧",
        "serviceScene":"商用",
        "deliveryManagerPhone":"18623116211",
        "projectName":"2测试单子",
        "deliveryManagerEmail":"18623116211@139.com",
        "deliveryManager":"的大"
    }
}

二、Mysql 解析语句

 select
	case when data_json like '%productType%' then replace(json_extract(replace(replace(json_extract(data_json,'$.productInfos'),'[',''),']',''),'$.productType'),'"','') else null end  productType_json, 
	t.product_types productType_sys, 
	replace(json_extract(json_extract(data_json,'$.orderInfo'),'$.productArrangementNumber'),'"','') productArrangementNumber_json,  
	product_arrangement_number productArrangementNumber_sys, 
	json_extract(json_extract(data_json,'$.orderInfo'),'$.productOrderNumber') productOrderNumber,  
	case when data_json like '%cnId%' then replace(json_extract(json_extract(replace(replace(json_extract(data_json,'$.productInfos'),'[',''),']',''),'$.productCode'),'$.cnId'),'"','') else null end cnId_json, 
	t.product_nums cnId_sys, 
	data_json,t.*
from mcm_base.opr_foreign_order_recorde t where t.id  not in ('20264336896c480984c3b7b92eb41','fe428dca36d54022a5cb41d512e57');