场景:查询结果封装成json格式
实现:封装记录为json格式可以编写自定义函数,也可以直接使用concat函数直接拼接,下面直接使用concat函数拼接实现将数据行转化为json数据
select concat('{\"id\":\"',
t.id,
'\",\"index_date\":\"',
NVL(t.index_date, ''),
'\",\"index_name\":\"',
NVL(t.index_name, ''),
'\",\"index_value\":\"',
NVL(t.index_value, ''),
'\"}') as value
from tbl_test t
注意:concat函数在连接元素时,需要注意如某一个元素为空时,concat函数直接返回null,所以需要对null元素做特殊处理,这里是将null转为空字符串。
hive 有直接解析 json 数据的函数 get_json_object(stringjson_string, string path)
将上述json数据封装完成后,我们存入表 tbl_test_json,插入字段 json_line 中
使用get_json_object函数解析该json数据:
select get_json_object(t.json_line, '$.id'),
get_json_object(t.json_line, '$.index_date'),
get_json_object(t.json_line, '$.index_name'),
get_json_object(t.json_line, '$.index_value')
from tbl_test_json t;
PS:get_json_object使用说明:
A limited version of JSONPath is supported:
$ : Root object
. : Child operator
[] : Subscript operator for array
* : Wildcard for []
Syntax not supported that's worth noticing:
: Zero length string as key
.. : Recursive descent
@ : Current object/element
() : Script expression
?() : Filter (script) expression.
[,] : Union operator
[start:end.step] : array slice operator
Example: src_json table is a single column (json), single row table:
+----+ json +----+
{"store":
{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}
+----+
The fields of the json object can be extracted using these queries:
hive> SELECT get_json_object(src_json.json, '$.owner') FROM src_json;
amy
hive> SELECT get_json_object(src_json.json, '$.store.fruit\[0]') FROM src_json;
{"weight":8,"type":"apple"}
hive> SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json;
NULL