半結構化資料

Redshift 入門

Jason Myers

Principal Architect

半結構化資料

儲存在 Redshift 的 SUPER 型別中
針對不同格式有專用的 SQL 函式
JSON 是一種半結構化資料

JSON 驗證函式

IS_VALID_JSON：檢查整個 JSON 物件是否有效

SELECT IS_VALID_JSON('{"one":1, "two":2}');

IS_VALID_JSON
=============
true

SELECT IS_VALID_JSON('{"one":1, "two":2');

IS_VALID_JSON
=============
false

IS_VALID_JSON_ARRAY：檢查 JSON 陣列是否有效。

SELECT IS_VALID_JSON_ARRAY('{"one":1}')

IS_VALID_JSON_ARRAY
===================
false

SELECT IS_VALID_JSON_ARRAY('[1,2,3]')

IS_VALID_JSON_ARRAY
===================
true

從 JSON 物件擷取

JSON_EXTRACT_PATH_TEXT 回傳指定 JSON 路徑的值
先給 JSON 字串或欄位，再依序給一個或多個鍵作為路徑

SELECT JSON_EXTRACT_PATH_TEXT('{"one":1, "two":2}', 'one');

JSON_EXTRACT_PATH_TEXT
======================
1

嘗試解析無效的 JSON

-- 嘗試從不良格式的 JSON 擷取路徑
SELECT JSON_EXTRACT_PATH_TEXT('{"one":1, "two":2', 'one');

JSON parsing error
DETAIL:  
  =========================================
  error:  JSON parsing error
  code:      8001
  context:   invalid json object 
  query:     [child_sequence:'one']
  location:  funcs_json.hpp:202
  process:   padbmaster [pid=1073807529]
  =========================================

從巢狀 JSON 路徑擷取

提供多個鍵作為參數

SELECT JSON_EXTRACT_PATH_TEXT('
         {
           "one_object":{
              "nested_three": 3, 
              "nested_four":4
           }, 
           "two":2
         }', 
         'one_object', 'nested_three');

JSON_EXTRACT_PATH_TEXT
======================
3

從不存在的巢狀 JSON 路徑擷取

回傳 NULL

SELECT JSON_EXTRACT_PATH_TEXT('
       {
         "one_object":{
            "nested_three": 3, 
            "nested_four":4
         }, 
         "two":2
       }', 
       'two', 'nested_five');

JSON_EXTRACT_PATH_TEXT
======================
NULL

從 JSON 陣列擷取

JSON_EXTRACT_ARRAY_ELEMENT_TEXT 回傳指定陣列索引的值
先給 JSON 字串或欄位，再給從 0 開始的索引整數。

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('[1.1,400,13]', 2);

JSON_EXTRACT_ARRAY_ELEMENT_TEXT
===============================
13

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('[1.1,400,13]', 3);

JSON_EXTRACT_ARRAY_ELEMENT_TEXT
===============================
NULL

從巢狀 JSON 陣列擷取

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT(
           JSON_EXTRACT_PATH_TEXT('
               {
                 "one":1, 
                 "nested_two":[3,4,5]
               }', 
               -- 先用 JSON_EXTRACT_PATH_TEXT 擷取 nested_two 的值
               'nested_two'
            ),
            -- 再用 JSON_EXTRACT_ARRAY_ELEMENT_TEXT 擷取陣列位置 1 的元素
            1
       );

JSON_EXTRACT_ARRAY_ELEMENT_TEXT
===============================
4

從巢狀 JSON 陣列擷取捷徑

陣列索引需為字串

-- 傳入兩個鍵（'nested_two'、'1'）以選取
-- nested_two 對應陣列值的第二個元素
SELECT JSON_EXTRACT_PATH_TEXT('
          {
            "one":1, 
            "nested_two":[3,4,5]
          }', 
          'nested_two', '1'
       );

JSON_EXTRACT_PATH_TEXT
======================
4

在 CTE 中轉型

WITH location_details AS (
    SELECT '{
        "location": "Salmon Challis National Forest",
      }'::SUPER::VARCHAR AS data
)

可作為 location_details.data 存取

一起來練習吧！

Redshift 入門