The GET_JSON_OBJECT function extracts an JSON object from a JSON string. This topic describes how to use GET_JSON_OBJECT in Hologres.

Limits

  • For each database, an extension can be created only once in one schema. For example, if an extension is created in the public schema of a database, no extension can be created in other schemas of the database.
  • You can create an extension in the pg_catalog system schema. This way, the GET_JSON_OBJECT function can be used in all schemas of all databases.
  • Extensions can be created only by superusers.

Syntax

The GET_JSON_OBJECT function is used in the following syntax. Before you use GET_JSON_OBJECT, you must create an extension first. 
CREATE extension if not exists hive_compatible [schema $(schema_name)];-- The extension can be created only by a superuser.
SELECT get_json_object ( json_string, path )

Parameters

The following table describes the parameters in GET_JSON_OBJECT:
ParameterDescription
json_stringThe JSON object. The value is of the TEXT type. It must be a valid JSON string.
pathThe JSON path that specifies the object to extract. The dollar sign ($) represents a JSON variable. The dot operator (.) or the square brackets ([]) are used to access a member of an array element.

If the JSON string is invalid, NULL is returned.

Example

create extension if not exists hive_compatible schema pg_catalog;

 
create table hive_json_example(    
    col_json text
);
 

insert into hive_json_example values('{"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(col_json, '$.owner') from hive_json_example;
--Result: amy

select get_json_object(col_json, '$.store.bicycle.price') from hive_json_example;
--Result: 19.95

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

select get_json_object(col_json, '$.no_key') from hive_json_example;
--Result: NULL

Common errors

  • ERROR: function get_json_object (text, unknown) does not exist
    • Possible cause 1

      In the schema-level permission model (SLPM), the RAM user does not have permissions to query the schema in which the extension is created. For example, the RAM user does not have permissions to query the public schema in which the extension is created.

    • Solution 1
      • Grant the RAM user permissions to query the schema.
      • Execute the following statements to create the extension in the pg_catalog schema. This way, all users have permissions to query this schema. 
        drop extension hive_compatible;
create extension hive_compatible schema pg_catalog;
    • Possible cause 2

      The first parameter of GET_JSON_OBJECT is not of the TEXT type.

    • Solution 2

      Convert the data type of the first parameter into TEXT.

  • ERROR: get_json_object for fe, should not be evaluated
    • Possible cause 1

      The first parameter of GET_JSON_OBJECT is a constant.

    • Solution 1

      Set the first parameter to the name of a table column.

    • Possible cause 2

      The first parameter of GET_JSON_OBJECT contains a value of NULL.

    • Solution 2

      Remove the NULL value from the first parameter.