7. Hive解析JSON字符串、JSON数组

Hive解析JSON字符串

1. get_json_object

• 语法：get_json_object(json_string, path)
  • json_string 是要解析的JSON字符串
  • path 是用于指定要提取的字段路径的字符串

-- 示例1(单层JSON)
SELECT get_json_object('{
                            "name": "John",
                            "age": 30
                         }', '$.name');
-- res: "John"

-- 示例2(嵌套JSON)
SELECT get_json_object('{
                            "person": {
                                "name": "John",
                                "age": 30,
                                "address": {
                                    "street": "123 Main St",
                                    "city": "New York"
                                }
                            }
                         }', '$.person.address.street');
-- res: "123 Main St"

局限性

• get_json_object 函数的性能会受到 JSON数据的结构和大小 的影响。对于较复杂的嵌套结构，考虑使用Hive的其他函数或自定义函数来处理JSON数据可能更合适。
• get_json_object 函数每次只能返回一个数据项。

2. json_tuple

• 语法：json_tuple(json_string, field1, field2, ...)
  • json_string 是要解析的JSON字符串
  • field1、field2 ... 是要提取的字段名

-- 示例1(单层JSON)
select json_tuple('{
                        "name": "zhangsan",
                        "age": 18
                    }','name','age');
-- res: zhangsan	18

-- 示例2(嵌套JSON)
SELECT json_tuple('{
                        "person": {
                            "name": "Alice",
                            "age": 25,
                            "address": {
                                "city": "New York",
                                "country": "USA"
                            }
                        }
                    }', 'person.name', 'person.age', 'person.address.city');
-- res: Alice    25    New York

Hive解析JSON数组

前置知识

explode函数

• 语法：explode(Array OR Map)
• 说明：explode() 函数接收一个 array 或者 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出，即将 hive 一列中复杂的 array 或者 map 结构拆分成多行显示

-- 解析 array
hive> select explode(array('A','B','C'));
OK
A
B
C

-- 解析map
hive> select explode(map('A',10,'B',20,'C',30));
OK
A       10
B       20
C       30

regexp_replace函数

• 语法： regexp_replace(string A, string B, string C)

• 说明：将 字符串A中 符合 java正则表达式B 的部分替换为C。注意，在有些情况下要使用转义字符，类似oracle中的regexp_replace函数。

hive> select regexp_replace('foobar', 'oo|ar', ''); 
OK
fb

1. 嵌套子查询解析JSON数组（使用explode+regexp_replace）

数据如下所示：

array(json_str)
[{“website”:“baidu.com”,“name”:“百度”},{“website”:“google.com”,“name”:“谷歌”}]

解析出其中的website、name

website	name
baidu.com	百度
google.com	谷歌

-- 思路
-- 1. 使用 regexp_replace 函数将原数据转换为 {"website":"baidu.com","name":"百度"};{"website":"google.com","name":"谷歌"}
-- 2. 使用 split 函数按照 ';' 分割 {"website":"baidu.com","name":"百度"};{"website":"google.com","name":"谷歌"},返回 [{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]
-- 3. 使用 explode 炸裂为
-- {"website":"baidu.com","name":"百度"}
-- {"website":"google.com","name":"谷歌"}
-- 4. 使用 json_tuple 解析数据

-- 实现
-- 1. 先将json数组中的元素解析出来，转化为每行显示
SELECT explode(split(regexp_replace(regexp_replace(
                                            '[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]',
                                            '\\[|\\]', ''), '\\}\\,\\{', '\\}\\;\\{'), '\\;'));

-- 2. 使用 json_tuple 解析数据
select json_tuple(json, 'website', 'name') 
from (
select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) 
as json) t1;

上面 regexp_replace 函数中的内容解析：

SELECT explode(split(
    regexp_replace(
        regexp_replace(
            '[
                {"website":"baidu.com","name":"百度"},
                {"website":"google.com","name":"谷歌"}
            ]', 
            '\\[|\\]' , ''), --将json数组两边的中括号去掉
            
              '\\}\\,\\{' , '\\}\\;\\{'), --将json数组元素之间的逗号换成分号
                
                 '\\;') --以分号作为分隔符(split函数以分号作为分隔)
          );
          
-- 问：为什么要将json数组中元素之间的逗号换成分号？
-- 答：因为元素内的分隔也是逗号，如果不将元素之间的逗号换掉的话，后面用split函数分隔时也会把元素内的数据给分隔，这不是我们想要的结果。

2. 使用 lateral view 解析JSON数组

当我们数据的结构如下，

ids	names
[1,2,3]	[{“name”: “daming”, “age”: “15”}, {“name”: “lingling”, “age”: “14”}, {“name”: “tom”, “age”: “17”}]

我们想要获取的数据为，

id	name
1	daming
2	daming
3	daming
1	lingling
2	lingling
3	lingling
1	tom
2	tom
3	tom

with json_data as (
    select `array`(1, 2, 3)                        as ids,
           `array`('{"name": "daming", "age": "15"}', '{"name": "lingling", "age": "14"}',
                   '{"name": "tom", "age": "17"}') as json_infos
)

-- 使用json_tuple()报错: org.apache.hadoop.hive.ql.parse.SemanticException:
-- UDTF's are not supported outside the SELECT clause, nor nested in expressions
-- 原因: 未知

-- SELECT id, json_tuple(json_info, 'name')

SELECT id, get_json_object(json_info, '$.name')
FROM json_data
         lateral view explode(json_data.ids) tmp_ids as id
         lateral view explode(json_data.json_infos) tmp_json_infos as json_info;

学习链接

文心一言

ChatGPT

Hive解析Json数组超全讲解