Contoh penggunaan data semi-terstruktur di Amazon Redshift

Contoh berikut menunjukkan cara bekerja dengan data semi-terstruktur di Amazon Redshift menggunakan sintaks PartiQL. Anda akan membuat tabel sampel untuk memuat kumpulan sampel data semi-terstruktur, lalu kueri objek data semi-terstruktur dalam berbagai kasus penggunaan.

catatan

Kami menyarankan Anda mengatur opsi enable_case_sensitive_identifier dan enable_case_sensitive_super_attribute konfigurasi sebelum bekerja dengan tipe data SUPER. Untuk informasi selengkapnya, lihat enable_case_sensitive_identifier dan enable_case_sensitive_super_attribute.

Memuat data semi-terstruktur

Pernyataan berikut membuat tabel sampel dan memuat objek JSON sampel ke dalam kolom all_data SUPER.


DROP TABLE IF EXISTS test_json;

SET enable_case_sensitive_super_attribute TO true;
SET enable_case_sensitive_identifier TO true;

CREATE TABLE test_json (all_data SUPER);

INSERT INTO test_json VALUES (JSON_PARSE('
{
   "data":{
      "pnr":{
         "type":"pnr",
         "pnrid":"123PQRS-2024-09-20",
         "bookingIdentifier":"123PQRS",
         "version":"5",
         "triggerType":"",
         "events":[
            {
               "eventType":"UPDATED",
               "type":"PART",
               "id":"123PQRS-2024-09-20-HO-1"
            },
            {
               "eventType":"CREATED",
               "type":"ABC",
               "id":"123PQRS-2024-09-20-OT-38"
            }
         ],
         "create":{
            "pnrCreateDate":"2024-09-20T16:56:00Z",
            "officeID":"OFFCID1234",
            "officeIDCategory":"Email"
         },
         "lastModification":{
            "dateTime":"2024-09-20T17:09:00Z"
         },
         "PARTDetails":[
            {
               "path":"1",
               "TrainPARTs":[
                  {
                     "PARTID":"123PQRS-2024-09-20-HO-1",
                     "departure":{
                        "departureStation":"XYZ",
                        "departureTimeLocal":"2024-10-03T06:30:00",
                        "departureTimeGMT":"2024-10-03T10:30:00Z"
                     },
                     "arrival":{
                        "arrivalStation":"ABC",
                        "arrivalTimeLocal":"2024-10-03T08:20:00",
                        "arrivalTimeGMT":"2024-10-03T15:20:00Z"
                     },
                     "marketing":{
                        "carrierCode":"XX",
                        "TrainNumber":"100"
                     },
                     "operating":{
                        "carrierCode":"YY",
                        "TrainNumber":"100-A"
                     },
                     "status":"ON",
                     "aircraft":{
                        "code":"222"
                     },
                     "class":"WC",
                     "first":"Y",
                     "seating":[
                        
                     ]
                  }
               ]
            }
         ],
         "commuterInformation":[
            {
               "commuterID":"2",
               "commuterPNR":"123PQRS-2024-09-20-RO-2",
               "commuterTypeCode":"DOM",
               "firstName":"JOHN",
               "lastName":"MILLER"
            }
         ],
         "contactDetail":[
            {
               "emailContacts":[
                  {
                     "id":"123PQRS-2024-09-20-OT-4",
                     "contact":"JOHNMILLER@EXAMPLE.COM",
                     "purpose":[
                        "BUSINESS"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ],
                     "language":"EN"
                  },
                  {
                     "id":"123PQRS-2024-09-20-OT-5",
                     "contact":"HARVEYCORMIER@EXAMPLE.COM",
                     "purpose":[
                        "NOTIFICATION"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ],
                     "language":"EN"
                  }
               ]
            },
            {
               "phoneContacts":[
                  {
                     "id":"123PQRS-2024-09-20-OT-3",
                     "contact":"1234567890",
                     "purpose":[
                        "NOTIFICATION"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ],
                     "language":""
                  }
               ]
            },
            {
               "addressInfo":[
                  {
                     "id":"123PQRS-2024-09-20-OT-6",
                     "addressline":[
                        "112 PORT STREET"
                     ],
                     "provinceState":"CA",
                     "cityName":"SAN JOSE",
                     "postalCode":"12345",
                     "countryCode":"USA",
                     "purpose":[
                        "MAILING"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ]
                  }
               ]
            }
         ],
         "PendingService":[
            {
               "id":"123PQRS-2024-09-20-OT-26",
               "code":"MONO",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ123 Seat-No 567",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            },
            {
               "id":"123PQRS-2024-09-20-OT-27",
               "code":"OTHS",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ567 Seat-No 111",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            },
            {
               "id":"123PQRS-2024-09-20-OT-28",
               "code":"OTHS",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ890 Seat-No 123",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            },
            {
               "id":"123PQRS-2024-09-20-OT-29",
               "code":"OTHS",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ111 Seat-No 333",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            }            
         ],
         "parts": [
            {
               "partname": "prop",
               "manufacturer": "local parts co",
               "quality": 2,
               "price": 10.00
            },
            {
               "partname": "prop",
               "manufacturer": "big parts co",
               "quality": null,
               "price": 9.00
            },
            {
               "partname": "prop",
               "manufacturer": "small parts co",
               "quality": 1,
               "price": 12.00
            },
            {
               "partname": "rudder",
               "manufacturer": "local parts co",
               "quality": 1,
               "price": 2.50
            },
            {
               "partname": "rudder",
               "manufacturer": "big parts co",
               "quality": 2,
               "price": 3.75
            },
            {
               "partname": "rudder",
               "manufacturer": "small parts co",
               "quality": null,
               "price": 1.90
            },
            {
               "partname": "wing",
               "manufacturer": "local parts co",
               "quality": null,
               "price": 7.50
            },
            {
               "partname": "wing",
               "manufacturer": "big parts co",
               "quality": 1,
               "price": 15.20
            },
            {
               "partname": "wing",
               "manufacturer": "small parts co",
               "quality": null,
               "price": 11.80
            }
         ],
         "count_by_color": [
            {
               "quality": "high",
               "red": 15,
               "green": 20,
               "blue": 7
            },
            {
               "quality": "normal",
               "red": 35,
               "green": null,
               "blue": 40
            },
            {
               "quality": "low",
               "red": 10,
               "green": 23,
               "blue": null
            }
         ]
       }
   },
   "id":"abcdefgh-ijklmnop-qrstuvwxyz123",
   "mainIds":[
      {
         "ID":"pqrstuvwxyz-aabbcc123",
         "Source":"NYC"
      }
   ]
}
'));

Menanyakan data semi-terstruktur bersarang

Pernyataan berikut menggunakan notasi titik PartiQL untuk mengekstrak pnrid bidang, yang bersarang tiga tingkat jauh di dalam objek tingkat atas. all_data


select all_data.data.pnr.pnrid::varchar from test_json;

 pnrid
--------------------
 123PQRS-2024-09-20

Pernyataan berikut menggunakan notasi braket PartiQL untuk menentukan dan mengekstrak hanya elemen pertama dari events array bersarang di dalam objek tingkat atas.


SELECT
    all_data.data.pnr.events[0]
FROM test_json;

 events
---------------------------------
{
   "eventType":"UPDATED",
   "type":"PART",
   "id":"123PQRS-2024-09-20-HO-1"
}

Pernyataan berikut mengekstrak eventType properti hanya elemen tertentu dari events array.


SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;

 eventtype
-----------
 "UPDATED"

Pernyataan berikut

Menggunakan `enable_case_sensitive_identifier` dan `enable_case_sensitive_super_attribute` dengan data semi-terstruktur

Contoh berikut menunjukkan bagaimana opsi konfigurasi enable_case_sensitive_identifier dan enable_case_sensitive_super_attribute perbedaan saat digunakan untuk kueri data semi-terstruktur. Untuk informasi selengkapnya tentang opsi konfigurasi ini, lihatMengakses bidang JSON dengan nama atau atribut bidang huruf besar dan huruf campuran.

Dalam pernyataan berikut, mengatur ulang kedua opsi konfigurasi ke default false membuat kueri mengembalikan NULL.


RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;

SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;

 eventtype
-----------
NULL

Dalam contoh berikut, kueri sampel mengembalikan hasil yang diinginkan setelah Anda membungkus atribut case sensitive dalam tanda kutip ganda dan disetel enable_case_sensitive_identifier ke true.


RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;

SELECT
    all_data.data.pnr.events[0]."eventType"
FROM test_json;
         
 eventtype
-----------
NULL

SET enable_case_sensitive_identifier TO true;
         
SELECT
    all_data.data.pnr.events[0]."eventType"
FROM test_json;
         
 eventtype
-----------
 "UPDATED"

Dalam contoh berikut, kueri sampel mengembalikan hasil yang diinginkan setelah Anda menyetel enable_case_sensitive_super_attribute ke true tanpa membungkus atribut case sensitive dalam tanda kutip ganda.


RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;

SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;
         
 eventtype
-----------
NULL
         
SET enable_case_sensitive_super_attribute TO true;
         
SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;
         
 eventtype
-----------
 "UPDATED"

Memfilter data semi-terstruktur

Pernyataan berikut menggunakan sintaks PartiQL dalam klausa WHERE dari pernyataan yang menghitung peristiwa dari UPDATED jenis untuk mengambil data dari atribut tertentu dari dalam array. Anda dapat menggunakan sintaks ini di setiap bagian dari query di mana Anda biasanya akan referensi kolom.


SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType = 'UPDATED';

 count
------
 1

Contoh berikut menggunakan braket dan titik sintaks PartiQL di kedua klausa GROUP BY dan ORDER BY.


SELECT all_data.data.pnr.events[0].eventType::varchar,
       COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType IS NOT NULL
GROUP BY all_data.data.pnr.events[0].eventType
ORDER BY all_data.data.pnr.events[0].eventType;

 eventtype | count
-----------+-------
 "UPDATED" | 1

Menghapus data semi-terstruktur

Pernyataan berikut menggunakan PartiQL bergabung untuk unnest array. events Perhatikan bahwa gabungan ini berfungsi bahkan ketika jumlah indeks untuk array tidak statis.

Untuk contoh unnesting data semi-terstruktur menggunakan UNNEST dalam klausa FROM, lihat. Contoh UNNEST


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
b.eventType::varchar event_type,
b.id::varchar event_id
FROM test_json a, 
  a.all_data.data.pnr.events b;

 type_info | pnr_id              | booking_id | version_info | event_type | event_id
-----------+---------------------+------------+--------------+------------+-------------------------
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | UPDATED    | 123PQRS-2024-09-20-HO-1
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | CREATED    | 123PQRS-2024-09-20-OT-38

Array bersarang yang tidak bersarang

Pernyataan berikut menggunakan PartiQL bergabung untuk unnest array yang bersarang di dalam array lain.

Untuk contoh unnesting data semi-terstruktur menggunakan UNNEST dalam klausa FROM, lihat. Contoh UNNEST


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact,
e::varchar email_purpose,
f::varchar email_commuter
FROM test_json a,
  a.all_data.data.pnr.contactDetail c,
  c."emailContacts" d,
  d.purpose e,
  d.commuter f;

 type_info | pnr_id              | booking_id | version_info | email_record_id         | email_contact             | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM    | BUSINESS      | 123PQRS-2024-09-20-RO-2
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | 123PQRS-2024-09-20-OT-5 | HARVEYCORMIER@EXAMPLE.COM | NOTIFICATION  | 123PQRS-2024-09-20-RO-2

Menggunakan data semi-terstruktur dalam subquery

Pernyataan berikut menggunakan subquery dalam klausa WHERE untuk mengembalikan hanya subbagian dari hasil dari contoh sebelumnya.


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact
FROM test_json a,
a.all_data.data.pnr.contactDetail c,
c."emailContacts" d
WHERE (SELECT COUNT(*) FROM d.purpose e WHERE e = 'BUSINESS') > 0;

 type_info | pnr_id              | booking_id | version_info | email_record_id         | email_contact             | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM    | BUSINESS      | 123PQRS-2024-09-20-RO-2

Menggabungkan kueri menggunakan data semi-terstruktur

Pernyataan berikut menggunakan fungsi COUNT untuk mengumpulkan jumlah elemen dalam PendingService array.


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a, 
  a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;

 type_info | pnr_id             | booking_id | version_info | total_pending_service
-----------+--------------------+------------+--------------+-----------------------
 pnr       | 123PQRS-2024-09-20 | 123PQRS    | 5            | 4

Menggunakan data semi-terstruktur dalam tampilan terwujud

Pernyataan berikut menggunakan pernyataan dari contoh sebelumnya untuk membuat tampilan terwujud. Tampilan terwujud secara otomatis menyegarkan jumlah layanan yang tertunda saat tabel dasar mendapatkan data baru.


CREATE MATERIALIZED VIEW mv_total_pending_service
AUTO REFRESH YES
AS
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a,
  a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;

Menggunakan PIVOT dan UNPIVOT dengan data semi-terstruktur

Pernyataan berikut menggunakan PIVOT pada partname kolom untuk mengembalikan harga rata-rata setiap bagian.


SELECT *
FROM 
(
SELECT
c.partname::varchar, c.price
FROM test_json a, 
  a.all_data.data.pnr.parts c) 
PIVOT (AVG(price) for partname IN ('prop', 'rudder', 'wing'));
            
 
 prop       | rudder             |  wing
------------+--------------------+--------
 10.33      | 2.71               |  11.50

Pada contoh sebelumnya, hasilnya diubah menjadi kolom. Contoh berikut menunjukkan kueri GROUP BY yang mengembalikan harga rata-rata dalam baris, bukan di kolom.


SELECT partname, avg(price)
FROM (
SELECT
c.partname::varchar, c.price
FROM test_json a, 
  a.all_data.data.pnr.parts c)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;
            
 partname |  avg
----------+-------
 prop     | 10.33
 rudder   |  2.71
 wing     | 11.50

Berikut ini adalah contoh PIVOT dengan manufacturer sebagai kolom implisit.


SELECT *
FROM (
SELECT
c.quality, c.manufacturer::varchar
FROM test_json a, 
  a.all_data.data.pnr.parts c) PIVOT (
count(*) FOR quality IN (1, 2, NULL)
);
            
 manufacturer      | 1  | 2  | null
-------------------+----+----+------
 local parts co    | 1  | 1  |  1
 big parts co      | 1  | 1  |  1
 small parts co    | 1  | 0  |  2

Berikut ini adalah contoh UNPIVOT pada kolom. quality


SELECT *
FROM 
(
SELECT
c.quality as quality
FROM test_json a, 
  a.all_data.data.pnr.parts c) 
UNPIVOT (cnt FOR column_header IN (quality));
            
 column_header   | cnt
-----------------+----
 quality         | 2    
 quality         | 1    
 quality         | 1    
 quality         | 2    
 quality         | 1

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Membuat kolom skalar Amazon Redshift dari data yang diparut

Machine learning