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