Dữ liệu bán cấu trúc

Giới thiệu về Redshift

Jason Myers

Principal Architect

Dữ liệu bán cấu trúc

Lưu trong kiểu SUPER của Redshift
Có hàm SQL riêng cho từng định dạng
JSON là dữ liệu bán cấu trúc

Hàm kiểm tra JSON

IS_VALID_JSON - kiểm tra toàn bộ đối tượng JSON hợp lệ

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 - kiểm tra mảng JSON hợp lệ

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

Trích xuất từ đối tượng JSON

JSON_EXTRACT_PATH_TEXT trả về giá trị tại đường dẫn JSON
Nhận chuỗi/field JSON và một hoặc nhiều khóa của đường dẫn

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

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

Phân tích JSON không hợp lệ

-- Cố trích xuất đường dẫn từ JSON sai định dạng
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]
  =========================================

Trích xuất từ đường dẫn JSON lồng nhau

Truyền nhiều khóa

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

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

Trích xuất từ đường dẫn JSON lồng nhau không tồn tại

Trả về NULL

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

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

Trích xuất từ mảng JSON

JSON_EXTRACT_ARRAY_ELEMENT_TEXT trả về giá trị tại chỉ mục mảng
Nhận chuỗi/field JSON và số nguyên chỉ mục bắt đầu từ 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

Trích xuất từ mảng JSON lồng nhau

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT(
           JSON_EXTRACT_PATH_TEXT('
               {
                 "one":1, 
                 "nested_two":[3,4,5]
               }', 
               -- Trích giá trị tại nested_two bằng JSON_EXTRACT_PATH_TEXT
               'nested_two'
            ),
            -- Lấy phần tử vị trí 1 của mảng bằng JSON_EXTRACT_ARRAY_ELEMENT_TEXT
            1
       );

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

Mẹo trích xuất từ mảng JSON lồng nhau

Chỉ mục mảng phải là chuỗi

-- Truyền hai khóa ('nested_two', '1') để chọn
-- phần tử thứ hai của giá trị mảng tại khóa nested_two
SELECT JSON_EXTRACT_PATH_TEXT('
          {
            "one":1, 
            "nested_two":[3,4,5]
          }', 
          'nested_two', '1'
       );

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

Ép kiểu trong CTE

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

Truy cập qua location_details.data

Ayo berlatih!

Giới thiệu về Redshift