Hologres:JSON functions

Prérequis

Avant d'utiliser GET_JSON_OBJECT, créez l'extension hive_compatible dans votre schéma. Pour plus d'informations, consultez la page Extensions.

CREATE EXTENSION IF NOT EXISTS hive_compatible SCHEMA <schema_name>;

Syntaxe

SELECT get_json_object(json_string, path);

Paramètres

Valeur renvoyée : la valeur extraite au format TEXT. Renvoie NULL si le chemin n'existe pas dans le code JSON ou si la chaîne JSON n'est pas valide.

Syntaxe des expressions de chemin

Une expression de chemin commence par $, qui représente une variable JSON. Utilisez les opérateurs suivants pour naviguer dans la structure JSON :

L'indexation des tableaux est basée sur zéro. $.items[0] renvoie le premier élément, tandis que $.items[1] renvoie le second.

Exemples

Préparez les exemples de données :

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

Extraire un champ de niveau supérieur — obtenez la valeur de $.owner :

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

Naviguer dans des objets imbriqués — obtenez le prix du vélo à l'emplacement $.store.bicycle.price :

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

Accéder à un élément de tableau par index — obtenez le premier élément à l'emplacement $.store.fruit[0] :

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

Clé inexistante — renvoie NULL lorsque le chemin n'existe pas :

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

Syntaxe

SELECT ROW_TO_JSON(record);

Paramètres

Valeur renvoyée : une chaîne JSON où chaque clé correspond à une colonne de l'enregistrement.

La convention de nommage des clés dépend de la version de Hologres :

Antérieur à la version V1.3.52 : Les clés sont positionnelles — f1 , f2 , etc.

Version V1.3.52 et ultérieure : Les clés sont basées sur les noms des colonnes.

Exemples

Préparez les exemples de données :

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

Convertissez chaque ligne en chaîne JSON :

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

Dans Hologres V1.3.52 et les versions ultérieures, les clés utilisent les noms des colonnes :

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

Dans les versions de Hologres antérieures à la V1.3.52, les clés utilisent des noms positionnels :

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 — Autorisations de schéma manquantes

Dans le modèle d'autorisations au niveau du schéma (SLPM), l'utilisateur RAM ne dispose pas des autorisations nécessaires pour interroger le schéma dans lequel l'extension a été créée (par exemple, le schéma public).

Pour résoudre ce problème, utilisez l'une des options suivantes :

• Accordez à l'utilisateur RAM l'autorisation d'interroger ce schéma.

• Recréez l'extension dans pg_catalog, auquel tous les utilisateurs peuvent accéder par défaut :

  DROP EXTENSION hive_compatible;
  CREATE EXTENSION hive_compatible SCHEMA pg_catalog;

Cause 2 — Le premier argument n'est pas de type TEXT

La fonction GET_JSON_OBJECT exige que le premier argument soit de type TEXT. Si la colonne possède un autre type de données, convertissez-la en TEXT :

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

ERROR: get_json_object for fe, should not be evaluated

Cause 1 — Le premier argument est une constante

La fonction GET_JSON_OBJECT n'accepte pas de constante de chaîne comme premier argument. Transmettez plutôt une référence de colonne de table :

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

Cause 2 — Le premier argument contient NULL

Supprimez les lignes où la colonne est NULL avant d'appeler la fonction :

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