原始json数据:
[{"website":"www.it.cn","name":"helloword"},{"website":"cloud.it.com","name":"carbondata 中文文档"}]
- --第一步操作 把json数组中元素的分隔符 从,替换成为; 避免切割的时候产生歧义
语句:
select regexp_replace('[{"website":"www.it.cn","name":"helloword"},{"website":"cloud.it.com","name":"carbondata 中文文档"}]', '\\}\\,\\{','\\}\\;\\{');
结果如下:
+----------------------------------------------------+--+
| _c0 |
+----------------------------------------------------+--+
| [{"website":"www.it.cn","name":"helloword"};{"website":"cloud.it.com","name":"carbondata 中文文档"}] |
+----------------------------------------------------+--+
- --第二步操作 把数据首尾的[]替换成为空
语句:
select regexp_replace(regexp_replace('[{"website":"www.it.cn","name":"helloword"},{"website":"cloud.it.com","name":"carbondata 中文文档"}]', '\\}\\,\\{','\\}\\;\\{'),'\\[|\\]','');
结果如下:
+----------------------------------------------------+--+
| _c0 |
+----------------------------------------------------+--+
| {"website":"www.it.cn","name":"helloword"};{"website":"cloud.it.com","name":"carbondata 中文文档"} |
+----------------------------------------------------+--+
- --第三步:根据分隔符;进行切割 返回的Array(String)
- --最后 :把切割的结果交给explode函数 炸开
select json_tuple(json, 'website', 'name') from (SELECT explode(split(regexp_replace(regexp_replace('[{"website":"www.it.cn","name":"helloword"},{"website":"cloud.it.com","name":"carbondata 中文文档"}]', '\\}\\,\\{','\\}\\;\\{'),'\\[|\\]',''),'\\;')) as json) it;