Hologres:Funções JSON

Pré-requisitos

Antes de usar GET_JSON_OBJECT, crie a extensão hive_compatible no seu schema. Para mais informações, consulte Extensões.

CREATE EXTENSION IF NOT EXISTS hive_compatible SCHEMA <schema_name>;

Sintaxe

SELECT get_json_object(json_string, path);

Parâmetros

Valor de retorno: O valor extraído como TEXT. Retorna NULL se o caminho não existir no JSON ou se a string JSON for inválida.

Sintaxe de expressão de caminho

Uma expressão de caminho começa com $, que representa uma variável JSON. Use os operadores abaixo para navegar pela estrutura JSON:

A indexação de arrays é baseada em zero. $.items[0] retorna o primeiro elemento, e $.items[1] retorna o segundo.

Exemplos

Configure os dados de exemplo:

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

Extrair um campo de nível superior — obtenha o valor de $.owner:

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

Navegar por objetos aninhados — obtenha o preço da bicicleta em $.store.bicycle.price:

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

Acessar um elemento de array por índice — obtenha o primeiro item em $.store.fruit[0]:

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

Chave inexistente — retorna NULL quando o caminho não existe:

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

Sintaxe

SELECT ROW_TO_JSON(record);

Parâmetros

Valor de retorno: Uma string JSON em que cada chave corresponde a uma coluna do registro.

A nomenclatura das chaves depende da versão do Hologres:

Antes da V1.3.52 : As chaves são posicionais — f1 , f2 , e assim por diante.

V1.3.52 e posterior : As chaves baseiam-se nos nomes das colunas.

Exemplos

Configure os dados de exemplo:

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

Converta cada linha em uma string JSON:

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

No Hologres V1.3.52 e posterior, as chaves usam os nomes das colunas:

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

Nas versões do Hologres anteriores à V1.3.52, as chaves usam nomes posicionais:

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 — Permissão de schema ausente

No modelo de permissão no nível de schema (SLPM), o usuário RAM não tem permissão para consultar o schema onde a extensão foi criada (por exemplo, o schema public).

Para corrigir, use uma das opções a seguir:

• Conceda ao usuário RAM permissão para consultar esse schema.

• Recrie a extensão em pg_catalog, acessível por padrão a todos os usuários:

  DROP EXTENSION hive_compatible;
  CREATE EXTENSION hive_compatible SCHEMA pg_catalog;

Causa 2 — O primeiro argumento não é TEXT

A função GET_JSON_OBJECT exige que o primeiro argumento seja do tipo TEXT. Se a coluna tiver outro tipo de dados, converta-o para TEXT:

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

ERROR: get_json_object for fe, should not be evaluated

Causa 1 — O primeiro argumento é uma constante

A função GET_JSON_OBJECT não aceita uma constante de string como primeiro argumento. Em vez disso, passe uma referência de coluna de tabela:

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

Causa 2 — O primeiro argumento contém NULL

Filtre as linhas em que a coluna é NULL antes de chamar a função:

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