实时数仓Hologres：JSON函数

GET_JSON_OBJECT

• 语法

  GET_JSON_OBJECT用于解析JSON对象。在使用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并返回。

• 语法

  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的值删除。