SQL解析JSON讲解
写这期之前，语兴及其他同学在开发使用时遇到很多不同JSON相关例子，期望通过这一期讲解能快速提升大家JSON解析效率
在解析JSON之前建议大家把JSON放到JSON解析相关站点或工具
1.正常JSON解析
样例数据
这里以一个社区动态数据为例子
{
"tag": "语兴好物好鞋分享",
"spuList": "Asics JOG 100 2 轻便透气入门级专业跑步鞋 黑武士 男女同款 ",
"circle": "",
"contentType": "动态图文",
"source": "APP发布",
"ocrResult": "[]",
"videoResult": "{}"
}
解析sql方法1（get_json_object函数）
select get_JSON_object(column_JSON,'$.tag') as tag
,get_JSON_object(column_JSON,'$.spuList') as spu_list--这里记得把解析好的字段名改一下
,get_JSON_object(column_JSON,'$.circle') as circle
,get_JSON_object(column_JSON,'$.contentType') as content_type
,column_JSON
from
(
select '{"tag":"语兴好物好鞋分享","spuList":"Asics JOG 100 2 轻便透气入门级专业跑步鞋 黑武士 男女同款 ","circle":"","contentType":"动态图文","source":"APP发布","ocrResult":"[]","videoResult":"{}"}' as column_JSON
)
解析sql方法2（json_tuple函数）,对比get_json_object可以获取多个字段
select tag
,spu_list
,content_type
from
(
select '{"tag":"语兴好物好鞋分享","spuList":"Asics JOG 100 2 轻便透气入门级专业跑步鞋 黑武士 男女同款 ","circle":"","contentType":"动态图文","source":"APP发布","ocrResult":"[]","videoResult":"{}"}' as column_JSON
)
LATERAL VIEW JSON_tuple(column_JSON, 'tag', 'spuList', 'contentType') b AS tag, spu_list, content_type;
--这里记得把解析好的字段名改一下
2.List套JSON解析
样例数据
这里以一个算法数据为例子,获取第一个小List 0:中的0:JSON field所对应的 spu_id
[
[
{
"dictCode": "",
"field": "spuId",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": 11924075,
"sourceCode": "repeat_image_check"
},
{
"dictCode": "repeat_image_similar_level#repeat_image_similar_level",
"field": "similarResult",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "enum",
"fieldVal": "high",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "spuTitle",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "Tommy Hilfiger 格子徽标尖领纽扣长袖衬衫 男款 蓝色",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "articleNumber",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "MW0MW33780-0MS",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "commodityOnlineStatus#commodityOnlineStatus",
"field": "status",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "enum",
"fieldVal": 1,
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "spuImage",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "image",
"fieldVal": "https://cdn.xx.com/pro-img/origin-img/20240522/e8b2386c27e44656a6e5fc46019c77e5.jpg",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "type",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "image",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "distance",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": 0.997498813867569,
"sourceCode": "repeat_image_check"
}
],
[
{
"dictCode": "",
"field": "spuId",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": 11924075,
"sourceCode": "repeat_image_check"
},
{
"dictCode": "repeat_image_similar_level#repeat_image_similar_level",
"field": "similarResult",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "enum",
"fieldVal": "high",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "spuTitle",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "Tommy Hilfiger 格子徽标尖领纽扣长袖衬衫 男款 蓝色",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "articleNumber",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "MW0MW33780-0MS",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "commodityOnlineStatus#commodityOnlineStatus",
"field": "status",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "enum",
"fieldVal": 1,
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "spuImage",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "image",
"fieldVal": "https://cdn.xx.com/pro-img/origin-img/20240522/e8b2386c27e44656a6e5fc46019c77e5.jpg",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "type",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "image",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "distance",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": 0.997498813867569,
"sourceCode": "repeat_image_check"
}
],
[
{
"dictCode": "",
"field": "spuId",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": 1332866,
"sourceCode": "repeat_image_check"
},
{
"dictCode": "repeat_image_similar_level#repeat_image_similar_level",
"field": "similarResult",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "enum",
"fieldVal": "high",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "spuTitle",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "MARKM NOUVELLE GENERATION 格纹撞色长袖衬衫 男女同款",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "articleNumber",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "MBBAI50020",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "commodityOnlineStatus#commodityOnlineStatus",
"field": "status",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "enum",
"fieldVal": 0,
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "spuImage",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "image",
"fieldVal": "https://cdn.xx.com/pro-img/origin-img/20220520/46984a1785d94c5ab389a0af6f8e431f.jpg",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "type",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": "image",
"sourceCode": "repeat_image_check"
},
{
"dictCode": "",
"field": "distance",
"fieldCategory": "out",
"fieldSource": "comp",
"fieldType": "text",
"fieldVal": 0.9969316732883453,
"sourceCode": "repeat_image_check"
}
]
]
你会发现这个是一个大List套小List套JSON
解析sql方法
SELECT
get_JSON_object(colun_JSON, '$[0][0].fieldVal') as spu_id,
get_JSON_object(colun_JSON, '$[0][1].fieldVal') as similar_result,
get_JSON_object(colun_JSON, '$[0][2].fieldVal') as spu_title,
get_JSON_object(colun_JSON, '$[0][3].fieldVal') as article_number,
get_JSON_object(colun_JSON, '$[0][4].fieldVal') as status,
get_JSON_object(colun_JSON, '$[0][5].fieldVal') as spu_image,
get_JSON_object(colun_JSON, '$[0][7].fieldVal') as distance,
get_JSON_object(colun_JSON, '$[0][8].fieldVal') as original_spu_id,
get_JSON_object(colun_JSON, '$[0][9].fieldVal') as original_spu_title,
get_JSON_object(colun_JSON, '$[0][10].fieldVal') as original_article_number
from
(
select '[[{"dictCode":"","field":"spuId","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":11924075,"sourceCode":"repeat_image_check"},{"dictCode":"repeat_image_similar_level#repeat_image_similar_level","field":"similarResult","fieldCategory":"out","fieldSource":"comp","fieldType":"enum","fieldVal":"high","sourceCode":"repeat_image_check"},{"dictCode":"","field":"spuTitle","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"Tommy Hilfiger 格子徽标尖领纽扣长袖衬衫 男款 蓝色","sourceCode":"repeat_image_check"},{"dictCode":"","field":"articleNumber","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"MW0MW33780-0MS","sourceCode":"repeat_image_check"},{"dictCode":"commodityOnlineStatus#commodityOnlineStatus","field":"status","fieldCategory":"out","fieldSource":"comp","fieldType":"enum","fieldVal":1,"sourceCode":"repeat_image_check"},{"dictCode":"","field":"spuImage","fieldCategory":"out","fieldSource":"comp","fieldType":"image","fieldVal":"https://cdn.xxxxx.com/pro-img/origin-img/20240522/e8b2386c27e44656a6e5fc46019c77e5.jpg","sourceCode":"repeat_image_check"},{"dictCode":"","field":"type","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"image","sourceCode":"repeat_image_check"},{"dictCode":"","field":"distance","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":0.997498813867569,"sourceCode":"repeat_image_check"}],[{"dictCode":"","field":"spuId","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":11924075,"sourceCode":"repeat_image_check"},{"dictCode":"repeat_image_similar_level#repeat_image_similar_level","field":"similarResult","fieldCategory":"out","fieldSource":"comp","fieldType":"enum","fieldVal":"high","sourceCode":"repeat_image_check"},{"dictCode":"","field":"spuTitle","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"Tommy Hilfiger 格子徽标尖领纽扣长袖衬衫 男款 蓝色","sourceCode":"repeat_image_check"},{"dictCode":"","field":"articleNumber","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"MW0MW33780-0MS","sourceCode":"repeat_image_check"},{"dictCode":"commodityOnlineStatus#commodityOnlineStatus","field":"status","fieldCategory":"out","fieldSource":"comp","fieldType":"enum","fieldVal":1,"sourceCode":"repeat_image_check"},{"dictCode":"","field":"spuImage","fieldCategory":"out","fieldSource":"comp","fieldType":"image","fieldVal":"https://cdn.xxxxx.com/pro-img/origin-img/20240522/e8b2386c27e44656a6e5fc46019c77e5.jpg","sourceCode":"repeat_image_check"},{"dictCode":"","field":"type","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"image","sourceCode":"repeat_image_check"},{"dictCode":"","field":"distance","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":0.997498813867569,"sourceCode":"repeat_image_check"}],[{"dictCode":"","field":"spuId","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":1332866,"sourceCode":"repeat_image_check"},{"dictCode":"repeat_image_similar_level#repeat_image_similar_level","field":"similarResult","fieldCategory":"out","fieldSource":"comp","fieldType":"enum","fieldVal":"high","sourceCode":"repeat_image_check"},{"dictCode":"","field":"spuTitle","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"MARKM NOUVELLE GENERATION 格纹撞色长袖衬衫 男女同款","sourceCode":"repeat_image_check"},{"dictCode":"","field":"articleNumber","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"MBBAI50020","sourceCode":"repeat_image_check"},{"dictCode":"commodityOnlineStatus#commodityOnlineStatus","field":"status","fieldCategory":"out","fieldSource":"comp","fieldType":"enum","fieldVal":0,"sourceCode":"repeat_image_check"},{"dictCode":"","field":"spuImage","fieldCategory":"out","fieldSource":"comp","fieldType":"image","fieldVal":"https://cdn.xxxxx.com/pro-img/origin-img/20220520/46984a1785d94c5ab389a0af6f8e431f.jpg","sourceCode":"repeat_image_check"},{"dictCode":"","field":"type","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":"image","sourceCode":"repeat_image_check"},{"dictCode":"","field":"distance","fieldCategory":"out","fieldSource":"comp","fieldType":"text","fieldVal":0.9969316732883453,"sourceCode":"repeat_image_check"}]]' as colun_JSON
)
3.JSON套JSON解析
样例数据
这里以一个语兴好物商品数据为例子，获取第一个data下brandName及color数据
{
"collectId": "farfetchcn_4bc68f664f1a03d79366a7c667993627_20240305191718",
"collectTime": "20240305191718",
"data": {
"attributes": "",
"brandName": "WARDROBE.NYC",
"category": "女士首页/WARDROBE.NYC/服装/西装夹克",
"color": "中性色",
"country": "",
"currency": "CNY",
"dataSource": "farfetch-cn",
"discountedPrice": "16447.0",
"itemNumber": "W4043R12",
"material": "表面: 羊毛,衬里: 粘胶纤维",
"productName": "WARDROBE.NYC Contour 双排扣西装夹克",
"productRetailPrice": "16447.0",
"productUrl": "https://www.farfetch.cn/cn/shopping/women/wardrobenyc-contour-item-19404158.aspx",
"region": "美国",
"releaseDate": "",
"size": [
[
{
"sizeList": [
"XXXS",
"XXS",
"XS",
"S",
"M",
"L",
"XL",
"XXL",
"XXXL",
"4XL",
"5XL",
"6XL",
"7XL"
],
"sizeStandardName": "服装标准尺码"
},
{
"sizeList": [
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60"
],
"sizeStandardName": "意大利"
},
{
"sizeList": [
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56"
],
"sizeStandardName": "法国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "英国尺码"
},
{
"sizeList": [
"0",
"2",
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24"
],
"sizeStandardName": "美国尺码"
},
{
"sizeList": [
"30",
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54"
],
"sizeStandardName": "德国/丹麦"
},
{
"sizeList": [
"XPP",
"PP",
"P",
"M",
"M",
"G",
"G",
"GG",
"GG",
"XGG",
"XGG",
"XGG",
"XGG"
],
"sizeStandardName": "巴西尺码 P-M-G"
},
{
"sizeList": [
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58"
],
"sizeStandardName": "巴西"
},
{
"sizeList": [
"3",
"5",
"7",
"9",
"11",
"13",
"15",
"17",
"19",
"21",
"23",
"25",
"27"
],
"sizeStandardName": "日本尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10"
],
"sizeStandardName": "标准尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"I",
"II",
"III",
"IV",
"V",
"VI",
"VII",
"VIII",
"IX",
"X"
],
"sizeStandardName": "罗马数字"
},
{
"sizeList": [
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60",
"62"
],
"sizeStandardName": "俄罗斯尺码"
},
{
"sizeList": [
null,
"33",
"44",
"55",
"66",
"77",
"88",
"99",
null,
null,
null,
null,
null
],
"sizeStandardName": "韩国"
},
{
"sizeList": [
"145/73A",
"150/76A",
"155/80A",
"160/84A",
"165/88A",
"170/92A",
"175/96A",
"180/100A",
"185/104A",
null,
null,
null,
null
],
"sizeStandardName": "中国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "澳大利亚尺码"
}
]
],
"skuId": "",
"spuId": "19421312312",
"style": "",
"targetPopulation": ""
}
}
解析sql方法
SELECT COALESCE(GET_JSON_OBJECT(column_JSON,'$.data.brandName'),'') as brand_name
,COALESCE(GET_JSON_OBJECT(column_JSON,'$.data.color'),'') as color
FROM
(
select '{
"collectId": "farfetchcn_4bc68f664f1a03d79366a7c667993627_20240305191718",
"collectTime": "20240305191718",
"data": {
"attributes": "",
"brandName": "WARDROBE.NYC",
"category": "女士首页/WARDROBE.NYC/服装/西装夹克",
"color": "中性色",
"country": "",
"currency": "CNY",
"dataSource": "farfetch-cn",
"discountedPrice": "16447.0",
"itemNumber": "W4043R12",
"material": "表面: 羊毛,衬里: 粘胶纤维",
"productName": "WARDROBE.NYC Contour 双排扣西装夹克",
"productRetailPrice": "16447.0",
"productUrl": "https://www.farfetch.cn/cn/shopping/women/wardrobenyc-contour-item-19404158.aspx",
"region": "美国",
"releaseDate": "",
"size": [
[
{
"sizeList": [
"XXXS",
"XXS",
"XS",
"S",
"M",
"L",
"XL",
"XXL",
"XXXL",
"4XL",
"5XL",
"6XL",
"7XL"
],
"sizeStandardName": "服装标准尺码"
},
{
"sizeList": [
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60"
],
"sizeStandardName": "意大利"
},
{
"sizeList": [
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56"
],
"sizeStandardName": "法国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "英国尺码"
},
{
"sizeList": [
"0",
"2",
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24"
],
"sizeStandardName": "美国尺码"
},
{
"sizeList": [
"30",
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54"
],
"sizeStandardName": "德国/丹麦"
},
{
"sizeList": [
"XPP",
"PP",
"P",
"M",
"M",
"G",
"G",
"GG",
"GG",
"XGG",
"XGG",
"XGG",
"XGG"
],
"sizeStandardName": "巴西尺码 P-M-G"
},
{
"sizeList": [
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58"
],
"sizeStandardName": "巴西"
},
{
"sizeList": [
"3",
"5",
"7",
"9",
"11",
"13",
"15",
"17",
"19",
"21",
"23",
"25",
"27"
],
"sizeStandardName": "日本尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10"
],
"sizeStandardName": "标准尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"I",
"II",
"III",
"IV",
"V",
"VI",
"VII",
"VIII",
"IX",
"X"
],
"sizeStandardName": "罗马数字"
},
{
"sizeList": [
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60",
"62"
],
"sizeStandardName": "俄罗斯尺码"
},
{
"sizeList": [
null,
"33",
"44",
"55",
"66",
"77",
"88",
"99",
null,
null,
null,
null,
null
],
"sizeStandardName": "韩国"
},
{
"sizeList": [
"145/73A",
"150/76A",
"155/80A",
"160/84A",
"165/88A",
"170/92A",
"175/96A",
"180/100A",
"185/104A",
null,
null,
null,
null
],
"sizeStandardName": "中国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "澳大利亚尺码"
}
]
],
"skuId": "",
"spuId": "19421312312",
"style": "",
"targetPopulation": ""
}
}'
as column_JSON
)
4.JSON套JSON且对字符串切割解析
样例数据
这里和3的数据一致，只不过对JSON套JSON解析后进行再次处理，这里将类别切割为3列，直接split即可
{
"collectId": "farfetchcn_4bc68f664f1a03d79366a7c667993627_20240305191718",
"collectTime": "20240305191718",
"data": {
"attributes": "",
"brandName": "WARDROBE.NYC",
"category": "女士首页/WARDROBE.NYC/服装/西装夹克",
"color": "中性色",
"country": "",
"currency": "CNY",
"dataSource": "farfetch-cn",
"discountedPrice": "16447.0",
"itemNumber": "W4043R12",
"material": "表面: 羊毛,衬里: 粘胶纤维",
"productName": "WARDROBE.NYC Contour 双排扣西装夹克",
"productRetailPrice": "16447.0",
"productUrl": "https://www.farfetch.cn/cn/shopping/women/wardrobenyc-contour-item-19404158.aspx",
"region": "美国",
"releaseDate": "",
"size": [
[
{
"sizeList": [
"XXXS",
"XXS",
"XS",
"S",
"M",
"L",
"XL",
"XXL",
"XXXL",
"4XL",
"5XL",
"6XL",
"7XL"
],
"sizeStandardName": "服装标准尺码"
},
{
"sizeList": [
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60"
],
"sizeStandardName": "意大利"
},
{
"sizeList": [
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56"
],
"sizeStandardName": "法国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "英国尺码"
},
{
"sizeList": [
"0",
"2",
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24"
],
"sizeStandardName": "美国尺码"
},
{
"sizeList": [
"30",
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54"
],
"sizeStandardName": "德国/丹麦"
},
{
"sizeList": [
"XPP",
"PP",
"P",
"M",
"M",
"G",
"G",
"GG",
"GG",
"XGG",
"XGG",
"XGG",
"XGG"
],
"sizeStandardName": "巴西尺码 P-M-G"
},
{
"sizeList": [
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58"
],
"sizeStandardName": "巴西"
},
{
"sizeList": [
"3",
"5",
"7",
"9",
"11",
"13",
"15",
"17",
"19",
"21",
"23",
"25",
"27"
],
"sizeStandardName": "日本尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10"
],
"sizeStandardName": "标准尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"I",
"II",
"III",
"IV",
"V",
"VI",
"VII",
"VIII",
"IX",
"X"
],
"sizeStandardName": "罗马数字"
},
{
"sizeList": [
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60",
"62"
],
"sizeStandardName": "俄罗斯尺码"
},
{
"sizeList": [
null,
"33",
"44",
"55",
"66",
"77",
"88",
"99",
null,
null,
null,
null,
null
],
"sizeStandardName": "韩国"
},
{
"sizeList": [
"145/73A",
"150/76A",
"155/80A",
"160/84A",
"165/88A",
"170/92A",
"175/96A",
"180/100A",
"185/104A",
null,
null,
null,
null
],
"sizeStandardName": "中国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "澳大利亚尺码"
}
]
],
"skuId": "",
"spuId": "19421312312",
"style": "",
"targetPopulation": ""
}
}
解析sql方法
SELECT
COALESCE(split(GET_JSON_OBJECT(column_JSON,'$.data.category'), '/')[0] ,'') as product_category_level1-- 商品一级类目
,COALESCE(split(GET_JSON_OBJECT(column_JSON,'$.data.category'), '/')[1] ,'') as product_category_level2-- 商品二级类目
,COALESCE(split(GET_JSON_OBJECT(column_JSON,'$.data.category'), '/')[2] ,'') as product_category_level3-- 商品三级类目
FROM
(
select '{
"collectId": "farfetchcn_4bc68f664f1a03d79366a7c667993627_20240305191718",
"collectTime": "20240305191718",
"data": {
"attributes": "",
"brandName": "WARDROBE.NYC",
"category": "女士首页/WARDROBE.NYC/服装/西装夹克",
"color": "中性色",
"country": "",
"currency": "CNY",
"dataSource": "farfetch-cn",
"discountedPrice": "16447.0",
"itemNumber": "W4043R12",
"material": "表面: 羊毛,衬里: 粘胶纤维",
"productName": "WARDROBE.NYC Contour 双排扣西装夹克",
"productRetailPrice": "16447.0",
"productUrl": "https://www.farfetch.cn/cn/shopping/women/wardrobenyc-contour-item-19404158.aspx",
"region": "美国",
"releaseDate": "",
"size": [
[
{
"sizeList": [
"XXXS",
"XXS",
"XS",
"S",
"M",
"L",
"XL",
"XXL",
"XXXL",
"4XL",
"5XL",
"6XL",
"7XL"
],
"sizeStandardName": "服装标准尺码"
},
{
"sizeList": [
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60"
],
"sizeStandardName": "意大利"
},
{
"sizeList": [
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56"
],
"sizeStandardName": "法国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "英国尺码"
},
{
"sizeList": [
"0",
"2",
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24"
],
"sizeStandardName": "美国尺码"
},
{
"sizeList": [
"30",
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54"
],
"sizeStandardName": "德国/丹麦"
},
{
"sizeList": [
"XPP",
"PP",
"P",
"M",
"M",
"G",
"G",
"GG",
"GG",
"XGG",
"XGG",
"XGG",
"XGG"
],
"sizeStandardName": "巴西尺码 P-M-G"
},
{
"sizeList": [
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58"
],
"sizeStandardName": "巴西"
},
{
"sizeList": [
"3",
"5",
"7",
"9",
"11",
"13",
"15",
"17",
"19",
"21",
"23",
"25",
"27"
],
"sizeStandardName": "日本尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10"
],
"sizeStandardName": "标准尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"I",
"II",
"III",
"IV",
"V",
"VI",
"VII",
"VIII",
"IX",
"X"
],
"sizeStandardName": "罗马数字"
},
{
"sizeList": [
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60",
"62"
],
"sizeStandardName": "俄罗斯尺码"
},
{
"sizeList": [
null,
"33",
"44",
"55",
"66",
"77",
"88",
"99",
null,
null,
null,
null,
null
],
"sizeStandardName": "韩国"
},
{
"sizeList": [
"145/73A",
"150/76A",
"155/80A",
"160/84A",
"165/88A",
"170/92A",
"175/96A",
"180/100A",
"185/104A",
null,
null,
null,
null
],
"sizeStandardName": "中国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "澳大利亚尺码"
}
]
],
"skuId": "",
"spuId": "19421312312",
"style": "",
"targetPopulation": ""
}
}'
as column_JSON
)
5.JSON套List
样例数据
这次我们取JSON中List语兴好物尺码数据，其实和第2点做法有异曲同工之妙
{
"collectId": "farfetchcn_4bc68f664f1a03d79366a7c667993627_20240305191718",
"collectTime": "20240305191718",
"data": {
"attributes": "",
"brandName": "WARDROBE.NYC",
"category": "女士首页/WARDROBE.NYC/服装/西装夹克",
"color": "中性色",
"country": "",
"currency": "CNY",
"dataSource": "farfetch-cn",
"discountedPrice": "16447.0",
"itemNumber": "W4043R12",
"material": "表面: 羊毛,衬里: 粘胶纤维",
"newProductImgLinks": [
"https://yuxingShoping517d4465ee74b5ca8d3805592b1c71fd",
"https://yuxingShoping1f8c975d69dfc306677002f4478c3bfb",
"https://yuxingShopingad3be11e6ec6a2edc3d9229951c522eb",
"https://yuxingShoping5bef91b8aa001506eb3400464db51e8f",
"https://yuxingShoping99c778fc6b051ec0afdb723613465051",
"https://yuxingShoping72bf817ae95662e58f8d73b8cf06b76e"
],
"productImgLinks": [
"https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287188_1000.jpg",
"https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287206_1000.jpg",
"https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287189_1000.jpg",
"https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287204_1000.jpg",
"https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287192_1000.jpg",
"https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287200_1000.jpg"
],
"productMainUrl": "https://yuxingShoping517d4465ee74b5ca8d3805592b1c71fd",
"productName": "WARDROBE.NYC Contour 双排扣西装夹克",
"productRetailPrice": "16447.0",
"productUrl": "https://www.farfetch.cn/cn/shopping/women/wardrobenyc-contour-item-19404158.aspx",
"region": "美国",
"releaseDate": "",
"size": [
[
{
"sizeList": [
"XXXS",
"XXS",
"XS",
"S",
"M",
"L",
"XL",
"XXL",
"XXXL",
"4XL",
"5XL",
"6XL",
"7XL"
],
"sizeStandardName": "服装标准尺码"
},
{
"sizeList": [
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60"
],
"sizeStandardName": "意大利"
},
{
"sizeList": [
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56"
],
"sizeStandardName": "法国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "英国尺码"
},
{
"sizeList": [
"0",
"2",
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24"
],
"sizeStandardName": "美国尺码"
},
{
"sizeList": [
"30",
"32",
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54"
],
"sizeStandardName": "德国/丹麦"
},
{
"sizeList": [
"XPP",
"PP",
"P",
"M",
"M",
"G",
"G",
"GG",
"GG",
"XGG",
"XGG",
"XGG",
"XGG"
],
"sizeStandardName": "巴西尺码 P-M-G"
},
{
"sizeList": [
"34",
"36",
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58"
],
"sizeStandardName": "巴西"
},
{
"sizeList": [
"3",
"5",
"7",
"9",
"11",
"13",
"15",
"17",
"19",
"21",
"23",
"25",
"27"
],
"sizeStandardName": "日本尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10"
],
"sizeStandardName": "标准尺码"
},
{
"sizeList": [
"000",
"00",
"0",
"I",
"II",
"III",
"IV",
"V",
"VI",
"VII",
"VIII",
"IX",
"X"
],
"sizeStandardName": "罗马数字"
},
{
"sizeList": [
"38",
"40",
"42",
"44",
"46",
"48",
"50",
"52",
"54",
"56",
"58",
"60",
"62"
],
"sizeStandardName": "俄罗斯尺码"
},
{
"sizeList": [
null,
"33",
"44",
"55",
"66",
"77",
"88",
"99",
null,
null,
null,
null,
null
],
"sizeStandardName": "韩国"
},
{
"sizeList": [
"145/73A",
"150/76A",
"155/80A",
"160/84A",
"165/88A",
"170/92A",
"175/96A",
"180/100A",
"185/104A",
null,
null,
null,
null
],
"sizeStandardName": "中国"
},
{
"sizeList": [
"4",
"6",
"8",
"10",
"12",
"14",
"16",
"18",
"20",
"22",
"24",
"26",
"28"
],
"sizeStandardName": "澳大利亚尺码"
}
]
],
"skuId": "",
"spuId": "19404158",
"style": "",
"targetPopulation": ""
}
}
解析sql方法
这里解析后你会发现还是一个list，如果要对size_list尺码进行进一步获取，需要再进行炸裂操作
SELECT
get_JSON_object(column_JSON,'$.data.size[0][0].sizeStandardName') as size_standard_name
,get_JSON_object(column_JSON,'$.data.size[0][0].sizeList') as size_list
FROM
(
select '{"collectId":"farfetchcn_4bc68f664f1a03d79366a7c667993627_20240305191718","collectTime":"20240305191718","data":{"attributes":"","brandName":"WARDROBE.NYC","category":"女士首页/WARDROBE.NYC/服装/西装夹克","color":"中性色","country":"","currency":"CNY","dataSource":"farfetch-cn","discountedPrice":"16447.0","itemNumber":"W4043R12","material":"表面: 羊毛,衬里: 粘胶纤维","newProductImgLinks":["https://yuxingShoping517d4465ee74b5ca8d3805592b1c71fd","https://yuxingShoping1f8c975d69dfc306677002f4478c3bfb","https://yuxingShopingad3be11e6ec6a2edc3d9229951c522eb","https://yuxingShoping5bef91b8aa001506eb3400464db51e8f","https://yuxingShoping99c778fc6b051ec0afdb723613465051","https://yuxingShoping72bf817ae95662e58f8d73b8cf06b76e"],"productImgLinks":["https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287188_1000.jpg","https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287206_1000.jpg","https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287189_1000.jpg","https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287204_1000.jpg","https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287192_1000.jpg","https://cdn-images.farfetch-contents.com/19/40/41/58/19404158_50287200_1000.jpg"],"productMainUrl":"https://yuxingShoping517d4465ee74b5ca8d3805592b1c71fd","productName":"WARDROBE.NYC Contour 双排扣西装夹克","productRetailPrice":"16447.0","productUrl":"https://www.farfetch.cn/cn/shopping/women/wardrobenyc-contour-item-19404158.aspx","region":"美国","releaseDate":"","size":[[{"sizeList":["XXXS","XXS","XS","S","M","L","XL","XXL","XXXL","4XL","5XL","6XL","7XL"],"sizeStandardName":"服装标准尺码"},{"sizeList":["36","38","40","42","44","46","48","50","52","54","56","58","60"],"sizeStandardName":"意大利"},{"sizeList":["32","34","36","38","40","42","44","46","48","50","52","54","56"],"sizeStandardName":"法国"},{"sizeList":["4","6","8","10","12","14","16","18","20","22","24","26","28"],"sizeStandardName":"英国尺码"},{"sizeList":["0","2","4","6","8","10","12","14","16","18","20","22","24"],"sizeStandardName":"美国尺码"},{"sizeList":["30","32","34","36","38","40","42","44","46","48","50","52","54"],"sizeStandardName":"德国/丹麦"},{"sizeList":["XPP","PP","P","M","M","G","G","GG","GG","XGG","XGG","XGG","XGG"],"sizeStandardName":"巴西尺码 P-M-G"},{"sizeList":["34","36","38","40","42","44","46","48","50","52","54","56","58"],"sizeStandardName":"巴西"},{"sizeList":["3","5","7","9","11","13","15","17","19","21","23","25","27"],"sizeStandardName":"日本尺码"},{"sizeList":["000","00","0","1","2","3","4","5","6","7","8","9","10"],"sizeStandardName":"标准尺码"},{"sizeList":["000","00","0","I","II","III","IV","V","VI","VII","VIII","IX","X"],"sizeStandardName":"罗马数字"},{"sizeList":["38","40","42","44","46","48","50","52","54","56","58","60","62"],"sizeStandardName":"俄罗斯尺码"},{"sizeList":[null,"33","44","55","66","77","88","99",null,null,null,null,null],"sizeStandardName":"韩国"},{"sizeList":["145/73A","150/76A","155/80A","160/84A","165/88A","170/92A","175/96A","180/100A","185/104A",null,null,null,null],"sizeStandardName":"中国"},{"sizeList":["4","6","8","10","12","14","16","18","20","22","24","26","28"],"sizeStandardName":"澳大利亚尺码"}]],"skuId":"","spuId":"19404158","style":"","targetPopulation":""}}' as column_JSON
)
6.单List解析
样例数据
[ 0:"http://yuxingShopping2Fks_live%2Fbbfc766bb259d6ebdc5fedc60a017967.jpg" 1:"http://yuxingShopping2Fks_live%2Fd1b554847c9606178a028a84285637c7.jpg" 2:"http://yuxingShopping2Fks_live%2F0c4a74495b1de720b9a918d04c6dab72.jpg" 3:"http://yuxingShopping2Fks_live%2F2a73008a74433520963bdc28d35b5862.jpg" 4:"http://yuxingShopping2Fks_live%2Fe54b003a3f9eb9e88bbf667dd2f9c1fa.jpg" 5:"http://yuxingShopping2Fks_live%2F0894950d96515ab8736e467464908589.jpg" 6:"http://yuxingShopping2Fks_live%2Fd7f5937f577edaf6db1c97610642227b.jpg" 7:"http://yuxingShopping2Fks_live%2F7cdd0e285a2f4d341700174c77cb1103.jpg" ]
解析sql方法
SELECT get_json_object(column_json, '$[0]') AS yuxing_shopping_image_url FROM table;
7.JSON暴力解析办法（比较蠢不建议用）
样例数据
解析sql方法
是不是看起很蠢，但如果实在不知道咋办了就用呗
8.最正确方法（直接问AIGC产品）
这里语兴更建议大家在解析复杂JSON时使用gpt/豆包/kimi/文心一言去查（自己写还是太慢了）
第一步打开文心一言、豆包、kimi等产品这里以豆包为例
第二步直接问他
提问方式为：如何使用hive sql 解析如下json中的xx所对应的值，这里放json信息
第三步拿去验证解析对不对，不对的话继续问
#数据人的面试交流地##牛客创作赏金赛##数据人offer决赛圈怎么选##数据分析##java#
阿里巴巴公司氛围 322人发布
查看12道真题和解析