ข้อมูลกึ่งโครงสร้าง

Introduction to Redshift

Jason Myers

Principal Architect

ข้อมูลกึ่งโครงสร้าง

เก็บในประเภท SUPER ของ Redshift
มีฟังก์ชัน SQL เฉพาะสำหรับรูปแบบต่าง ๆ
JSON เป็นหนึ่งในประเภทข้อมูลกึ่งโครงสร้าง

ฟังก์ชันตรวจสอบ JSON

IS_VALID_JSON - ตรวจสอบว่า JSON object ทั้งหมดถูกต้องหรือไม่

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 array ถูกต้องหรือไม่

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 object

JSON_EXTRACT_PATH_TEXT คืนค่าที่ JSON path ที่ระบุ
รับ JSON string หรือฟิลด์ แล้วตามด้วย key หนึ่งตัวหรือมากกว่า

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

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

การแยกข้อมูลจาก JSON ที่ไม่ถูกต้อง

-- Trying to extract a path from poorly formed 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 path แบบซ้อน

ระบุอาร์กิวเมนต์ key หลายตัวได้

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 path แบบซ้อนที่ไม่มีอยู่จริง

คืนค่า 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 array

JSON_EXTRACT_ARRAY_ELEMENT_TEXT คืนค่าที่ index ของ array
รับ JSON string หรือฟิลด์ แล้วตามด้วยเลข index (เริ่มจากศูนย์)

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 array แบบซ้อน

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT(
           JSON_EXTRACT_PATH_TEXT('
               {
                 "one":1, 
                 "nested_two":[3,4,5]
               }', 
               -- Extracting the value at nested_two with JSON_EXTRACT_PATH_TEXT
               'nested_two'
            ),
            -- Extracting item at position one of the array with JSON_EXTRACT_ARRAY_ELEMENT_TEXT
            1
       );

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

ทางลัดในการดึงข้อมูลจาก JSON array แบบซ้อน

Array index ต้องเป็น string

-- Passing two keys ('nested_two', '1') to select
-- the second element of the nested_two keys array value
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

มาฝึกกันเถอะ!

Introduction to Redshift