Hologres相关的JSON函数_实时数仓 Hologres(Hologres)-阿里云帮助中心

本文为您介绍实时数仓Hologres相关的JSON函数。

GET_JSON_OBJECT

GET_JSON_OBJECT用于解析JSON对象，文本为您介绍GET_JSON_OBJECT函数的用法。

语法

在使用GET_JSON_OBJECT函数前，需要先创建加载扩展（Extension），详情请参见Extension扩展。

--创建Extension
CREATE extension if NOT EXISTS hive_compatible schema $(schema_name);
SELECT get_json_object ( json_string, path );

参数说明

参数

描述

json_string

JSON对象变量，TEXT类型。格式为合法JSON格式字符串。

path

JSON内层对象访问变量。使用$表示JSON变量标识，通过 . 或[]读取JSON内层对象或数组。

如果您输入的JSON字符串无效，则系统返回NULL。

示例

准备示例数据。

--创建Extension
CREATE EXTENSION IF NOT EXISTS hive_compatible SCHEMA pg_catalog;
--准备示例数据
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"}');

示例1：从col_json列中查询JSON对象中$.owner路径下的数据。

--返回结果为：amy
SELECT
    get_json_object (col_json, '$.owner')
FROM
    hive_json_example;

示例2：从col_json列中查询JSON对象中$.store.bicycle.price路径下的数据。

--返回结果为：19.95
SELECT
    get_json_object (col_json, '$.store.bicycle.price')
FROM
    hive_json_example;

示例3：从col_json列中查询JSON对象中$.store.fruit路径下fruit数组的第一个元素（索引为0）。

-- 返回结果为：{"weight":8, "type":"apple"}
SELECT
    get_json_object (col_json, '$.store.fruit[0]')
FROM
    hive_json_example;

示例4：查看非JSON对象变量的数据。

--返回值为：NULL
SELECT
    get_json_object (col_json, '$.no_key')
FROM
    hive_json_example;

row_to_json

row_to_json函数支持将多个字符串或列（最多50列）拼接成一个JSON并返回。

说明

仅Hologres V1.3及以上版本支持row_to_json函数，若想要使用该函数您可以通过加入实时数仓Hologres交流群申请升级实例或实例升级，加群方式请参见如何获取更多的在线支持？。

语法
```
SELECT row_to_json(record)
```
参数说明
record：是一个行类型的参数，可以是表名、视图名或者查询结果。

示例

--准备测试数据
CREATE TABLE interests_test (
    name text,
    intrests text
);
INSERT INTO interests_test
    VALUES ('张三', '唱歌,跳舞'), ('李四', '踢球,跑步,画画'), ('王五', '插花,书法,弹琴,睡觉');

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

Hologres从V1.3.52版本开始，JSON中的Key支持根据列名生成。

V1.3.52以下版本返回结果如下。

row_to_json                
------------------------------
{"f1":"张三","f2":"唱歌,跳舞"}
{"f1":"李四","f2":"踢球,跑步,画画"}
{"f1":"王五","f2":"插花,书法,弹琴,睡觉"}

V1.3.52及以上版本返回结果如下。

row_to_json                
------------------------------
"{"name" : "王五", "intrests" : "插花,书法,弹琴,睡觉"}"
"{"name" : "张三", "intrests" : "唱歌,跳舞"}"
"{"name" : "李四", "intrests" : "踢球,跑步,画画"}"

常见报错

报错：ERROR：function get_json_object (text, unknown) does not exist。
- 可能原因一
  在SLPM模式下RAM用户没有创建extension所在Schema的查询权限（例如extension指定创建在名称为public的Schema下，RAM用户没有public的查询权限）。
- 解决方法一
  - 授予RAM用户Schema的查询权限。
  - 使用如下命令重新创建extension在pg_catalog下，所有账号都可查询。
```
drop extension hive_compatible;
create extension hive_compatible schema pg_catalog;
```
- 可能原因二
  GET_JSON_OBJECT的第一个参数不是TEXT类型。
- 解决方法二
  将第一个参数转换为TEXT类型。
报错：ERROR: get_json_object for fe, should not be evaluated。
- 可能原因一
  GET_JSON_OBJECT的第一个参数是常量。
- 解决方法一
  第一个参数使用表的列。
- 可能原因二
  GET_JSON_OBJECT的第一个参数含有为NULL的值。
- 解决方法二
  将第一个参数为NULL的值删除。