Data semiterstruktur

Pengantar Redshift

Jason Myers

Principal Architect

Data semiterstruktur

Disimpan dalam tipe SUPER Redshift
Memiliki fungsi SQL khusus untuk berbagai format
JSON adalah jenis data semiterstruktur

Fungsi Validasi JSON

IS_VALID_JSON - memastikan seluruh objek JSON valid

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 - memastikan array JSON valid.

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

Ekstraksi dari objek JSON

JSON_EXTRACT_PATH_TEXT mengembalikan nilai pada path JSON
Menerima string/kolom JSON lalu satu atau lebih key ke path

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

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

Mencoba mengurai JSON tidak valid

-- Mencoba mengekstrak path dari JSON yang tidak valid
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]
  =========================================

Ekstraksi dari path JSON bertingkat

Beri beberapa argumen 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

Ekstraksi dari path JSON bertingkat yang tidak ada

Mengembalikan NULL

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

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

Ekstraksi dari array JSON

JSON_EXTRACT_ARRAY_ELEMENT_TEXT mengembalikan nilai pada indeks array
Menerima string/kolom JSON lalu bilangan bulat indeks mulai dari nol.

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

Ekstraksi dari array JSON bertingkat

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT(
           JSON_EXTRACT_PATH_TEXT('
               {
                 "one":1, 
                 "nested_two":[3,4,5]
               }', 
               -- Mengambil nilai di nested_two dengan JSON_EXTRACT_PATH_TEXT
               'nested_two'
            ),
            -- Mengambil elemen ke-1 array dengan JSON_EXTRACT_ARRAY_ELEMENT_TEXT
            1
       );

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

Pintasan ekstraksi dari array JSON bertingkat

Indeks array harus berupa string

-- Memberi dua key ('nested_two', '1') untuk memilih
-- elemen kedua dari nilai array key nested_two
SELECT JSON_EXTRACT_PATH_TEXT('
          {
            "one":1, 
            "nested_two":[3,4,5]
          }', 
          'nested_two', '1'
       );

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

Casting di CTE

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

Dapat diakses sebagai location_details.data

Ayo berlatih!

Pengantar Redshift