set hive.fetch.task.conversion=more; DESCRIBE FUNCTION get_json_object; DESCRIBE FUNCTION EXTENDED get_json_object; CREATE TABLE dest1(c1 STRING) STORED AS TEXTFILE; FROM src INSERT OVERWRITE TABLE dest1 SELECT ' abc ' WHERE src.key = 86; set hive.fetch.task.conversion=more; EXPLAIN SELECT get_json_object(src_json.json, '$.owner') FROM src_json; SELECT get_json_object(src_json.json, '$') FROM src_json; SELECT get_json_object(src_json.json, '$.owner'), get_json_object(src_json.json, '$.store') FROM src_json; SELECT get_json_object(src_json.json, '$.store.bicycle'), get_json_object(src_json.json, '$.store.book') FROM src_json; SELECT get_json_object(src_json.json, '$.store.book[0]'), get_json_object(src_json.json, '$.store.book[*]') FROM src_json; SELECT get_json_object(src_json.json, '$.store.book[0].category'), get_json_object(src_json.json, '$.store.book[*].category'), get_json_object(src_json.json, '$.store.book[*].isbn'), get_json_object(src_json.json, '$.store.book[*].reader') FROM src_json; SELECT get_json_object(src_json.json, '$.store.book[*].reader[0].age'), get_json_object(src_json.json, '$.store.book[*].reader[*].age') FROM src_json; SELECT get_json_object(src_json.json, '$.store.basket[0][1]'), get_json_object(src_json.json, '$.store.basket[*]'), get_json_object(src_json.json, '$.store.basket[*][0]'), get_json_object(src_json.json, '$.store.basket[0][*]'), get_json_object(src_json.json, '$.store.basket[*][*]'), get_json_object(src_json.json, '$.store.basket[0][2].b'), get_json_object(src_json.json, '$.store.basket[0][*].b') FROM src_json; SELECT get_json_object(src_json.json, '$.non_exist_key'), get_json_object(src_json.json, '$..no_recursive'), get_json_object(src_json.json, '$.store.book[10]'), get_json_object(src_json.json, '$.store.book[0].non_exist_key'), get_json_object(src_json.json, '$.store.basket[*].non_exist_key'), get_json_object(src_json.json, '$.store.basket[0][*].non_exist_key') FROM src_json; SELECT get_json_object(src_json.json, '$.zip code') FROM src_json; SELECT get_json_object(src_json.json, '$.fb:testid') FROM src_json; -- Verify that get_json_object can handle new lines in JSON values CREATE TABLE dest2(c1 STRING) STORED AS RCFILE; INSERT OVERWRITE TABLE dest2 SELECT '{"a":"b\nc"}' FROM src tablesample (1 rows); SELECT * FROM dest2; SELECT get_json_object(c1, '$.a') FROM dest2; --root is array SELECT get_json_object('[1,2,3]', '$[0]'), get_json_object('[1,2,3]', '$.[0]'), get_json_object('[1,2,3]', '$.[1]'), get_json_object('[1,2,3]', '$[1]'), get_json_object('[1,2,3]', '$[2]'), get_json_object('[1,2,3]', '$[*]'), get_json_object('[1,2,3]', '$'), get_json_object('[{"k1":"v1"},{"k2":"v2"},{"k3":"v3"}]', '$[2]'), get_json_object('[{"k1":"v1"},{"k2":"v2"},{"k3":"v3"}]', '$[2].k3'), get_json_object('[[1,2,3],[4,5,6],[7,8,9]]', '$[1]'), get_json_object('[[1,2,3],[4,5,6],[7,8,9]]', '$[1][0]'), get_json_object('[{"k1":[{"k11":[1,2,3]}]}]', '$[0].k1[0].k11[1]'); --null SELECT get_json_object('[1,2,3]', '[2]'), get_json_object('[1,2,3]', '$0'), get_json_object('[1,2,3]', '$[3]');