本文为您介绍实时数仓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的值删除。