场景：查询结果封装成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