Impala JSON函数

在impala 6.3 版本中加入JSON解析函数，此前的版本则没有提供JSON解析函数需要自己编译安装。

The following characters are supported in the selector JSON path:
$ : Denotes the root object
. : Denotes the child operator
[] : Denotes the subscript operator for array
* : Denotes the wildcard for [] or .

示例：

 SELECT GET_JSON_OBJECT ('{"a":true, "b":false, "c":true}', '$.*') result

result
 [true,false,true]




SELECT GET_JSON_OBJECT(t.json, '$.a.b.c') FROM (VALUES (
 ('{"a": {"b": {"c": 1}}}' AS json),
 ('{"a": {"b": {"c": 2}}}'),
 ('{"a": {"b": {"c": 3}}}')
)) t


num
  1
  2
  3


SELECT GET_JSON_OBJECT(t.json, '$.a') a,
 GET_JSON_OBJECT(t.json, '$.b') b,
 GET_JSON_OBJECT(t.json, '$.c') c
FROM (VALUES (
 ('{"a":1, "b":2, "c":3}' AS json),
 ('{"b":2, "c":3}'),
 ('{"c":3}')
)) t

a	     b	    c
1	     2	    3
NULL	 2	    3
NULL	 NULL	3


SELECT GET_JSON_OBJECT(t.json, '$[1]'),
 GET_JSON_OBJECT(t.json, '$[*]')
FROM (VALUES (
 ('["a", "b", "c"]' AS json),
 ('["a", "b"]'),
 ('["a"]')
)) t

seq	items
 b	 ["a","b","c"]
 b	 ["a","b"]
 NULL	 a

参考：

https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_misc_functions.html#misc_functions__get_json_object

https://github.com/nazgul33/impala-get-json-object-udf