全網最全的SQL解析Json總結

一、HiveSQL中的Json解析對於從事數倉開發的同學Json解析是進行數倉開發中很很常會遇到的問題。相對於後端開發，提供了很多好用的Json解析工具包而言，SQL在json解析方面還是偏弱。而且有些解析語法如果不是經常用，其實也很容易遺忘。我大概總結了常用的SQL解析的三大分類，分別是直譯型、行轉列型、正則型二、直譯型

所謂直譯型，就是不用廢腦子的。傻瓜化函數解析，包括get_json_object、json_tuple.

2.1 get_json_object

•語法: get_json_object(json, args)

            •對於json中的key.直接用'$.key'標識            •對於json中的數組.用'$.key[0]'標識取key這個數組索引為0的值.如果本身就是一個json數組,那麼使用'$.[0]'標識取第一個元素.

select get_json_object('{"user_name":"suweipeng"}', '$.user_name');
select get_json_object('{"user_name":"suweipeng", "addrs":["北京","杭州","成都"]}', '$.addrs[0]');
2.2 json_tuplejson_tuple和get_json_object的關鍵區別在於其解析參數是可變參數。即可以接多個需要解析的json欄位,一次性解析出來。適用於需要一次性解析一個json中的多個欄位。其次json_tuple不用使用$標識要提取的欄位.
•語法: json_tuple(json, args...)•例子:
select json_tuple('{"user_name":"suweipeng", "company":"didi", "phone_number":18699999999}', 'user_name', 'company', 'phone_number');
三、行轉列型•很多時候, 我們會碰到遇到這種需求，由於後端開發表結構的設計原因，將一對多的關係直接塞進了json中。比如後端用user_id和addrs這兩個欄位，表示一個用戶對應的可能住址。其中addrs欄位的key為省份，value為具體城市。但是這樣的設計對於數倉開發不好用於分析。•PS: 我把一行轉成多列稱為行轉列，不同於網上定義的行轉列。user_idaddrs123456789[{"city":"福州"}, {"city":"北京"}]
我們希望將其轉換成如下表結構
user_idcity123456789福州123456789北京
對於這種類型的需求，我們就需要藉助lateral view語法.
3.1 json_split和lateral view的配合
•先用json_split將json字符串轉換成hive數組.•然後用explode將數組炸開.•lateral view可以理解為sql中的for循環寫法
那麼下面的sql就很指標了,將json數組轉換成Hive數組中後，一個個拿出來，再調用get_json_object解析.即可實現一行轉多列效果。
with tbl as (     select          '123456789' as user_id         ,'[{"city":"福州"}, {"city":"北京"}]' as addrs)select     user_id    ,get_json_object(json,'$.city') as cityfrom (select     user_id    ,json_split(addrs) json_arrfrom tbl) tlateral view explode(json_arr) jsons as json;
結果如下圖
3.2 json_tuple和lateral view的配合user_idaddrs123456789[{"city":"福州", "area":"鼓樓區"}, {"city":"北京": "area":"朝陽區"}]
既然lateral view等效於for循環.那麼later view就是可以嵌套的.所以對於上述addrs的解析.我們可以
•先使用一個for循環取出一個個的json.•再使用一個for循環藉助json_tuple函數解析出所有欄位.這是json_tuple經常會結合lateral view使用,因為這樣就可以json_tuple解析出的表，給予列名。
with tbl as (     select          '123456789' as user_id         ,'[{"city":"福州", "area":"鼓樓區"}, {"city":"北京","area":"朝陽區"}]' as addrs)select     user_id    ,city    ,areafrom (select     user_id    ,json_split(addrs) json_arrfrom tbl) tlateral view explode(json_arr) jsons as jsonlateral view json_tuple(json,'city', 'area') resultTbl as city, area;
3.3 出個難題user_idaddrs123456789[{"福建":"福州"}, {"北京":"北京"}]
將如上數據,用SQL轉換成如下表.
user_idprovincecity123456789福建福州123456789北京北京四、正則型基本上掌握了get_json_object和json_tuple還有lateral view配合其他循環函數的使用.能夠勝任99%的json解析工作。但是實際工作中會遇到一些比較變態的解析.格式可能並非json格式, 還需要進行解析,這時候只能用正則表達式來強行提取了。4.1 regexp_replace
語法: regexp_replace(json, 正則表達式, 替換值)
例子:
去掉所有中括號
select regexp_replace('[{"key1":"value1"},{"key2":"value2"}]','\\[|\\]','');
4.2 regexp_extract
語法: regexp_extract(json, 正則表達式, 分組索引).
•通常會結合()進行分組, 從1開始計數.•所以分組索引為1，表示取第一個()分組的值.•分組索引為0, 表示取整個字符串.
例子:
只去掉首末中擴號
•匹配以[開頭, ]結尾。並且中間至少有一個字符的字符串.•其中1表示取出中間那個字符串.
select regexp_extract('[{"key1":"value1"},{"key2":"value2"}]','^\\[(.+)\\]$',1)
結合正則-解決 3.3的難題
with tbl as (     select          '123456789' as user_id         ,'[{"福建":"福州"}, {"北京":"北京"}]' as addrs)select     user_id    ,regexp_extract(json,'^\\{\\"(.+)\\":\\"(.+)\\"\\}$', 1)  as province    ,regexp_extract(json,'^\\{\\"(.+)\\":\\"(.+)\\"\\}$', 2)  as cityfrom (select     user_id    ,json_split(addrs) json_arrfrom tbl) tlateral view explode(json_arr) jsons as json;
4.3 split
語法: split(str, 分割符)
•split函數經常結合explode爆炸函數使用, 因為split完後就是一個hive數組.•並且split的分割符,支持根據正則表達式進行分割.
五、總結
•通常的json解析，藉助上面的介紹的三種類型相互組合，可以解決99.99%的問題。•更複雜的json，如果用上述解析很麻煩，可以考慮寫UDF，用Java的方式解析json。這也是最容易，也是最麻煩的方式。最容易的方式在於用Java來解析json十分簡單，最麻煩的是還要進行打包，編譯，上傳到HDFS，進行函數註冊等。