This topic describes the syntax of the Hive-compatible function GET_JSON_OBJECT in Hologres.

Introduction

GET_JSON_OBJECT: extracts an object or array from a JSON string.

Syntax

The syntax of the GET_JSON_OBJECT function is as follows. This function belongs to the hive_compatible extension of Hologres. Before you call the GET_JSON_OBJECT function, make sure that the hive_compatible extension is created.

create extension hive_compatible;
select get_json_object (json_string, path )

Synopsis

  • json_string: the JSON string to be queried. The value is a variable of the TEXT type.
  • path: the JSON path that specifies the object or array to extract. Set the value in the format of $.Object name or array name or$[Object name or array name]. If the JSON string is invalid, NULL is returned.

Examples

create extension hive_compatible;
data =
{"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"
}

select get_json_object(data, '$.owner') from test;
Result: amy

select get_json_object(data, '$.name.bicycle.price') from test;
Result: 19.95

select get_json_object(data, '$.store.fruit[0]') from test;
Result: {"weight":8, "type":"apple"}