Hologres:JSON functions

Requisitos previos

Antes de usar GET_JSON_OBJECT, cree la extensión hive_compatible en su esquema. Para obtener más información, consulte Extensiones.

CREATE EXTENSION IF NOT EXISTS hive_compatible SCHEMA <schema_name>;

Sintaxis

SELECT get_json_object(json_string, path);

Parámetros

Valor de retorno: El valor extraído como TEXT. Devuelve NULL si la ruta no existe en el JSON o si la cadena JSON no es válida.

Sintaxis de expresiones de ruta

Una expresión de ruta comienza con $, que representa una variable JSON. Utilice los siguientes operadores para navegar por la estructura JSON:

La indexación de matrices se basa en 0. $.items[0] devuelve el primer elemento, y $.items[1] devuelve el segundo.

Ejemplos

Configure los datos de ejemplo:

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

Extraer un campo de nivel superior — obtenga el valor de $.owner:

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

Navegar por objetos anidados — obtenga el precio de la bicicleta en $.store.bicycle.price:

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

Acceder a un elemento de matriz por índice — obtenga el primer elemento en $.store.fruit[0]:

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

Clave inexistente — devuelve NULL cuando la ruta no existe:

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

Sintaxis

SELECT ROW_TO_JSON(record);

Parámetros

Valor de retorno: Una cadena JSON donde cada clave corresponde a una columna del registro.

La nomenclatura de las claves depende de la versión de Hologres:

Antes de V1.3.52 : Las claves son posicionales — f1 , f2 , y así sucesivamente.

V1.3.52 y posteriores : Las claves se basan en los nombres de las columnas.

Ejemplos

Configure los datos de ejemplo:

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

Convierta cada fila en una cadena JSON:

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

En Hologres V1.3.52 y posteriores, las claves utilizan los nombres de las columnas:

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"}"

En versiones de Hologres anteriores a V1.3.52, las claves utilizan nombres posicionales:

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

Causa 1 — Falta permiso de esquema

En el modelo de permisos a nivel de esquema (SLPM), el usuario RAM carece de permiso para consultar el esquema donde se creó la extensión (por ejemplo, el esquema public).

Para solucionar esto, utilice una de las siguientes opciones:

• Otorgue al usuario RAM permiso para consultar dicho esquema.

• Vuelva a crear la extensión en pg_catalog, al cual todos los usuarios pueden acceder de forma predeterminada:

  DROP EXTENSION hive_compatible;
  CREATE EXTENSION hive_compatible SCHEMA pg_catalog;

Causa 2 — El primer argumento no es TEXT

GET_JSON_OBJECT requiere que el primer argumento sea de tipo TEXT. Si la columna tiene un tipo de datos diferente, conviértala a TEXT:

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

ERROR: get_json_object for fe, should not be evaluated

Causa 1 — El primer argumento es una constante

GET_JSON_OBJECT no acepta una constante de cadena como primer argumento. Pase una referencia de columna de tabla en su lugar:

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

Causa 2 — El primer argumento contiene NULL

Elimine las filas donde la columna es NULL antes de llamar a la función:

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