PREHOOK: query: DESCRIBE FUNCTION get_json_object PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION get_json_object POSTHOOK: type: DESCFUNCTION get_json_object(json_txt, path) - Extract a json object from path PREHOOK: query: DESCRIBE FUNCTION EXTENDED get_json_object PREHOOK: type: DESCFUNCTION POSTHOOK: query: DESCRIBE FUNCTION EXTENDED get_json_object POSTHOOK: type: DESCFUNCTION get_json_object(json_txt, path) - Extract a json object from path Extract json object from a json string based on json path specified, and return json string of the extracted json object. It will return null if the input json string is invalid. A limited version of JSONPath 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 PREHOOK: query: CREATE TABLE dest1(c1 STRING) STORED AS TEXTFILE PREHOOK: type: CREATETABLE PREHOOK: Output: database:default PREHOOK: Output: default@dest1 POSTHOOK: query: CREATE TABLE dest1(c1 STRING) STORED AS TEXTFILE POSTHOOK: type: CREATETABLE POSTHOOK: Output: database:default POSTHOOK: Output: default@dest1 PREHOOK: query: FROM src INSERT OVERWRITE TABLE dest1 SELECT ' abc ' WHERE src.key = 86 PREHOOK: type: QUERY PREHOOK: Input: default@src PREHOOK: Output: default@dest1 POSTHOOK: query: FROM src INSERT OVERWRITE TABLE dest1 SELECT ' abc ' WHERE src.key = 86 POSTHOOK: type: QUERY POSTHOOK: Input: default@src POSTHOOK: Output: default@dest1 POSTHOOK: Lineage: dest1.c1 SIMPLE [] PREHOOK: query: EXPLAIN #### A masked pattern was here #### PREHOOK: type: QUERY POSTHOOK: query: EXPLAIN #### A masked pattern was here #### POSTHOOK: type: QUERY STAGE DEPENDENCIES: Stage-0 is a root stage STAGE PLANS: Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: TableScan alias: src_json Statistics: Num rows: 1 Data size: 644 Basic stats: COMPLETE Column stats: NONE Select Operator #### A masked pattern was here #### outputColumnNames: _col0 Statistics: Num rows: 1 Data size: 644 Basic stats: COMPLETE Column stats: NONE ListSink PREHOOK: query: SELECT get_json_object(src_json.json, '$') FROM src_json PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: SELECT get_json_object(src_json.json, '$') FROM src_json POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### amy {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"basket":[[1,2,{"b":"y","a":"x"}],[3,4],[5,6]],"book":[{"author":"Nigel Rees","title":"Sayings of the Century","category":"reference","price":8.95},{"author":"Herman Melville","title":"Moby Dick","category":"fiction","price":8.99,"isbn":"0-553-21311-3"},{"author":"J. R. R. Tolkien","title":"The Lord of the Rings","category":"fiction","reader":[{"age":25,"name":"bob"},{"age":26,"name":"jack"}],"price":22.99,"isbn":"0-395-19395-8"}],"bicycle":{"price":19.95,"color":"red"}} PREHOOK: query: SELECT get_json_object(src_json.json, '$.store.bicycle'), get_json_object(src_json.json, '$.store.book') FROM src_json PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: SELECT get_json_object(src_json.json, '$.store.bicycle'), get_json_object(src_json.json, '$.store.book') FROM src_json POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### {"price":19.95,"color":"red"} [{"author":"Nigel Rees","title":"Sayings of the Century","category":"reference","price":8.95},{"author":"Herman Melville","title":"Moby Dick","category":"fiction","price":8.99,"isbn":"0-553-21311-3"},{"author":"J. R. R. Tolkien","title":"The Lord of the Rings","category":"fiction","reader":[{"age":25,"name":"bob"},{"age":26,"name":"jack"}],"price":22.99,"isbn":"0-395-19395-8"}] PREHOOK: query: SELECT get_json_object(src_json.json, '$.store.book[0]'), get_json_object(src_json.json, '$.store.book[*]') FROM src_json PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: SELECT get_json_object(src_json.json, '$.store.book[0]'), get_json_object(src_json.json, '$.store.book[*]') FROM src_json POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### {"author":"Nigel Rees","title":"Sayings of the Century","category":"reference","price":8.95} [{"author":"Nigel Rees","title":"Sayings of the Century","category":"reference","price":8.95},{"author":"Herman Melville","title":"Moby Dick","category":"fiction","price":8.99,"isbn":"0-553-21311-3"},{"author":"J. R. R. Tolkien","title":"The Lord of the Rings","category":"fiction","reader":[{"age":25,"name":"bob"},{"age":26,"name":"jack"}],"price":22.99,"isbn":"0-395-19395-8"}] PREHOOK: query: 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 PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: 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 POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### reference ["reference","fiction","fiction"] ["0-553-21311-3","0-395-19395-8"] [{"age":25,"name":"bob"},{"age":26,"name":"jack"}] PREHOOK: query: 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 PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: 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 POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### 25 [25,26] PREHOOK: query: 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 PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: 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 POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### 2 [[1,2,{"b":"y","a":"x"}],[3,4],[5,6]] [1,3,5] [1,2,{"b":"y","a":"x"}] [1,2,{"b":"y","a":"x"},3,4,5,6] y ["y"] PREHOOK: query: 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 PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: 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 POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### NULL NULL NULL NULL NULL NULL PREHOOK: query: SELECT get_json_object(src_json.json, '$.zip code') FROM src_json PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: SELECT get_json_object(src_json.json, '$.zip code') FROM src_json POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### 94025 PREHOOK: query: SELECT get_json_object(src_json.json, '$.fb:testid') FROM src_json PREHOOK: type: QUERY PREHOOK: Input: default@src_json #### A masked pattern was here #### POSTHOOK: query: SELECT get_json_object(src_json.json, '$.fb:testid') FROM src_json POSTHOOK: type: QUERY POSTHOOK: Input: default@src_json #### A masked pattern was here #### 1234 PREHOOK: query: -- Verify that get_json_object can handle new lines in JSON values CREATE TABLE dest2(c1 STRING) STORED AS RCFILE PREHOOK: type: CREATETABLE PREHOOK: Output: database:default PREHOOK: Output: default@dest2 POSTHOOK: query: -- Verify that get_json_object can handle new lines in JSON values CREATE TABLE dest2(c1 STRING) STORED AS RCFILE POSTHOOK: type: CREATETABLE POSTHOOK: Output: database:default POSTHOOK: Output: default@dest2 PREHOOK: query: INSERT OVERWRITE TABLE dest2 SELECT '{"a":"b\nc"}' FROM src tablesample (1 rows) PREHOOK: type: QUERY PREHOOK: Input: default@src PREHOOK: Output: default@dest2 POSTHOOK: query: INSERT OVERWRITE TABLE dest2 SELECT '{"a":"b\nc"}' FROM src tablesample (1 rows) POSTHOOK: type: QUERY POSTHOOK: Input: default@src POSTHOOK: Output: default@dest2 POSTHOOK: Lineage: dest2.c1 SIMPLE [] PREHOOK: query: SELECT * FROM dest2 PREHOOK: type: QUERY PREHOOK: Input: default@dest2 #### A masked pattern was here #### POSTHOOK: query: SELECT * FROM dest2 POSTHOOK: type: QUERY POSTHOOK: Input: default@dest2 #### A masked pattern was here #### {"a":"b c"} PREHOOK: query: SELECT get_json_object(c1, '$.a') FROM dest2 PREHOOK: type: QUERY PREHOOK: Input: default@dest2 #### A masked pattern was here #### POSTHOOK: query: SELECT get_json_object(c1, '$.a') FROM dest2 POSTHOOK: type: QUERY POSTHOOK: Input: default@dest2 #### A masked pattern was here #### b c PREHOOK: query: --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]') PREHOOK: type: QUERY PREHOOK: Input: _dummy_database@_dummy_table #### A masked pattern was here #### POSTHOOK: query: --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]') POSTHOOK: type: QUERY POSTHOOK: Input: _dummy_database@_dummy_table #### A masked pattern was here #### 1 1 2 2 3 [1,2,3] [1,2,3] {"k3":"v3"} v3 [4,5,6] 4 2 PREHOOK: query: --null SELECT get_json_object('[1,2,3]', '[2]'), get_json_object('[1,2,3]', '$0'), get_json_object('[1,2,3]', '$[3]') PREHOOK: type: QUERY PREHOOK: Input: _dummy_database@_dummy_table #### A masked pattern was here #### POSTHOOK: query: --null SELECT get_json_object('[1,2,3]', '[2]'), get_json_object('[1,2,3]', '$0'), get_json_object('[1,2,3]', '$[3]') POSTHOOK: type: QUERY POSTHOOK: Input: _dummy_database@_dummy_table #### A masked pattern was here #### NULL NULL NULL