Hologres:JSON functions

Prerequisites

Before using GET_JSON_OBJECT, create the hive_compatible extension in your schema. For more information, see Extensions.

CREATE EXTENSION IF NOT EXISTS hive_compatible SCHEMA <schema_name>;

Syntax

SELECT get_json_object(json_string, path);

Parameters

Return value: The extracted value as TEXT. Returns NULL if the path does not exist in the JSON or if the JSON string is invalid.

Path expression syntax

A path expression starts with $, which represents a JSON variable. Use the following operators to navigate the JSON structure:

Array indexing is 0-based. $.items[0] returns the first element, and $.items[1] returns the second.

Examples

Set up sample data:

-- Create the extension in pg_catalog so all users can access it.
CREATE EXTENSION IF NOT EXISTS hive_compatible SCHEMA pg_catalog;

-- Create and populate the sample table.
BEGIN;
CREATE TABLE hive_json_example (
    col_json text
);
COMMIT;

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"}');

Extract a top-level field — get the value of $.owner:

-- Returns: amy
SELECT get_json_object(col_json, '$.owner')
FROM hive_json_example;

Navigate nested objects — get the bicycle price at $.store.bicycle.price:

-- Returns: 19.95
SELECT get_json_object(col_json, '$.store.bicycle.price')
FROM hive_json_example;

Access an array element by index — get the first item at $.store.fruit[0]:

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

Non-existent key — returns NULL when the path does not exist:

-- Returns: NULL
SELECT get_json_object(col_json, '$.no_key')
FROM hive_json_example;

Syntax

SELECT ROW_TO_JSON(record);

Parameters

Return value: A JSON string where each key corresponds to a column in the record.

Key naming depends on the Hologres version:

Before V1.3.52: Keys are positional — f1, f2, and so on.

V1.3.52 and later: Keys are based on column names.

Examples

Set up sample data:

CREATE TABLE interests_test (
    name    text,
    intrests text
);

INSERT INTO interests_test VALUES
  ('Ava', 'singing, dancing'),
  ('Bob', 'playing football, running, painting'),
  ('Jack', 'arranging flowers, writing calligraphy, playing the piano, sleeping');

Convert each row to a JSON string:

SELECT ROW_TO_JSON(t)
FROM (
    SELECT name, intrests
    FROM interests_test
) AS t;

In Hologres V1.3.52 and later, keys use column names:

row_to_json
------------------------------
"{"name": "Jack", "interests": "arranging flowers, writing calligraphy, playing the piano, sleeping"}"
"{"name": "Ava", "interests": "singing, dancing"}"
"{"name": "Bob", "interests": "playing football, running, painting"}"

In Hologres versions earlier than V1.3.52, keys use positional names:

row_to_json
------------------------------
{"f1":"Ava","f2":"singing, dancing"}
{"f1":"Bob","f2":"playing football, running, painting"}
{"f1":"Jack","f2":"arranging flowers, writing calligraphy, playing the piano, sleeping"}

ERROR: function get_json_object (text, unknown) does not exist

Cause 1 — Missing schema permission

In the schema-level permission model (SLPM), the RAM user lacks permission to query the schema where the extension was created (for example, the public schema).

To fix this, use one of the following options:

• Grant the RAM user permission to query that schema.

• Re-create the extension in pg_catalog, which all users can access by default:

  DROP EXTENSION hive_compatible;
  CREATE EXTENSION hive_compatible SCHEMA pg_catalog;

Cause 2 — First argument is not TEXT

GET_JSON_OBJECT requires the first argument to be of TEXT type. If the column has a different data type, cast it to TEXT:

SELECT get_json_object(col::text, '$.key') FROM your_table;

ERROR: get_json_object for fe, should not be evaluated

Cause 1 — First argument is a constant

GET_JSON_OBJECT does not accept a string constant as the first argument. Pass a table column reference instead:

-- Correct: pass a column reference
SELECT get_json_object(col_json, '$.key') FROM your_table;

Cause 2 — First argument contains NULL

Remove rows where the column is NULL before calling the function:

SELECT get_json_object(col_json, '$.key')
FROM your_table
WHERE col_json IS NOT NULL;