Amazon Redshift で半構造化データを使用する例 - Amazon Redshift
半構造化データのロードネストされた半構造化データのクエリ半構造化データで enable_case_sensitive_identifier および enable_case_sensitive_super_attribute を使用する半構造化データのフィルタリング半構造化データのネスト解除ネストされた配列のネスト解除サブクエリでの半構造化データの使用半構造化データを使用したクエリの集計マテリアライズドビューでの半構造化データの使用 半構造化データで PIVOT および UNPIVOT を使用する

Amazon Redshift は、2025 年 11 月 1 日以降、新しい Python UDF の作成をサポートしなくなります。Python UDF を使用する場合は、その日付より前に UDF を作成してください。既存の Python UDF は引き続き通常どおり機能します。詳細については、ブログ記事を参照してください。

Amazon Redshift で半構造化データを使用する例

次の例は、PartiQL 構文を使用して Amazon Redshift で半構造化データを操作する方法を示しています。サンプルテーブルを作成して半構造化データのサンプルセットをロードし、さまざまなユースケースで半構造化データオブジェクトをクエリします。

注記

SUPER データ型を使用する前に、enable_case_sensitive_identifier および enable_case_sensitive_super_attribute 設定オプションを設定することをお勧めします。詳細については、「enable_case_sensitive_identifier」および「enable_case_sensitive_super_attribute」を参照してください。

半構造化データのロード

次のステートメントは、サンプルテーブルを作成し、サンプル JSON オブジェクトを SUPER all_data 列にロードします。

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"
      }
   ]
}
'));

ネストされた半構造化データのクエリ

次のステートメントでは、PartiQL のドット表記を使用して pnrid フィールドを抽出します。このフィールドは、最上位 all_data オブジェクトの深さ 3 レベルにネストされています。

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

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

次のステートメントでは、PartiQL の角括弧表記を使用して、最上位オブジェクト内にネストされた events 配列から最初の要素のみを指定および抽出します。

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

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

次のステートメントは、events 配列から指定された要素のみの eventType プロパティを抽出します。

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

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

以下のステートメントの場合

半構造化データで enable_case_sensitive_identifier および enable_case_sensitive_super_attribute を使用する

次の例は、半構造化データへのクエリに使用するときに、設定オプション enable_case_sensitive_identifier と enable_case_sensitive_super_attribute がどのように異なるかを示しています。これらの設定オプションの詳細については、「大文字、および大小文字が混在するフィールド名または属性を持つ JSON フィールドへのアクセス」を参照してください。

次のステートメントでは、両方の設定オプションをデフォルトの false にリセットすると、クエリは 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

次の例では、大文字と小文字を区別する属性を二重引用符で囲み、enable_case_sensitive_identifier を 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"

次の例では、大文字と小文字を区別する属性を二重引用符で囲まずに enable_case_sensitive_super_attribute を 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_super_attribute TO true;
         
SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;
         
 eventtype
-----------
 "UPDATED"

半構造化データのフィルタリング

次のステートメントでは、UPDATED タイプのイベントをカウントするステートメントの WHERE 句で PartiQL 構文を使用して、配列内から特定の属性のデータを取得します。この構文は、通常列を参照するクエリの任意の部分で使用できます。

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

 count
------
 1

次の例では、GROUP BY 句と ORDER BY 句の両方で PartiQL の角括弧とドットの構文を使用します。

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

半構造化データのネスト解除

次のステートメントでは、PartiQL 結合を使用して events 配列のネストを解除します。この結合は、配列のインデックス数が静的でない場合でも機能することに注意してください。

FROM 句で UNNEST を使用して半構造化データをネスト解除する例については、「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

ネストされた配列のネスト解除

次のステートメントでは、PartiQL 結合を使用して、別の配列内にネストされた配列のネストを解除します。

FROM 句で UNNEST を使用して半構造化データをネスト解除する例については、「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

サブクエリでの半構造化データの使用

次のステートメントでは、WHERE 句のサブクエリを使用して、前の例の結果の一部のみを返します。

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

半構造化データを使用したクエリの集計

次のステートメントでは、COUNT 関数を使用して PendingService 配列内の要素の数を集計します。

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

マテリアライズドビューでの半構造化データの使用

次のステートメントでは、前の例のステートメントを使用してマテリアライズドビューを作成します。マテリアライズドビューは、ベーステーブルが新しいデータを取得すると、保留中のサービスの数を自動的に更新します。

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;

半構造化データで PIVOT および UNPIVOT を使用する

次のステートメントでは、partname 列の PIVOT を使用して、各パートの平均価格を返します。

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

前の例では、結果が列に変換されています。次の例は、平均価格を列ではなく行で返す GROUP BY クエリを示しています。

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

manufacturer を暗黙的な列として使用する PIVOT の例を次に示します。

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

quality 列の UNPIVOT の例を次に示します。

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