半構造化データのクエリ¶
Snowflakeでは、特殊な演算子と関数を使用して半構造化データにアクセスする SQL クエリをサポートしています。このトピックは、JSON、Avro、ORC、およびParquetデータに適用されることにご注意ください。このトピックは XML データには適用されません。
このトピックの内容：
例で使用されるサンプルデータ¶
特に断りのない限り、このトピックの例では、 src という名前の単一の VARIANT 列を含む car_sales という名前のテーブルを参照しています。
CREATE OR REPLACE TABLE car_sales
(
src variant
)
AS
SELECT PARSE_JSON(column1) AS src
FROM VALUES
('{
"date" : "2017-04-28",
"dealership" : "Valley View Auto Sales",
"salesperson" : {
"id": "55",
"name": "Frank Beasley"
},
"customer" : [
{"name": "Joyce Ridgely", "phone": "16504378889", "address": "San Francisco, CA"}
],
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
}'),
('{
"date" : "2017-04-28",
"dealership" : "Tindel Toyota",
"salesperson" : {
"id": "274",
"name": "Greg Northrup"
},
"customer" : [
{"name": "Bradley Greenbloom", "phone": "12127593751", "address": "New York, NY"}
],
"vehicle" : [
{"make": "Toyota", "model": "Camry", "year": "2017", "price": "23500", "extras":["ext warranty", "rust proofing", "fabric protection"]}
]
}') v;
SELECT * FROM car_sales;
+-------------------------------------------+
| SRC |
|-------------------------------------------|
| { |
| "customer": [ |
| { |
| "address": "San Francisco, CA", |
| "name": "Joyce Ridgely", |
| "phone": "16504378889" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Valley View Auto Sales", |
| "salesperson": { |
| "id": "55", |
| "name": "Frank Beasley" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "paint protection" |
| ], |
| "make": "Honda", |
| "model": "Civic", |
| "price": "20275", |
| "year": "2017" |
| } |
| ] |
| } |
| { |
| "customer": [ |
| { |
| "address": "New York, NY", |
| "name": "Bradley Greenbloom", |
| "phone": "12127593751" |
| } |
| ], |
| "date": "2017-04-28", |
| "dealership": "Tindel Toyota", |
| "salesperson": { |
| "id": "274", |
| "name": "Greg Northrup" |
| }, |
| "vehicle": [ |
| { |
| "extras": [ |
| "ext warranty", |
| "rust proofing", |
| "fabric protection" |
| ], |
| "make": "Toyota", |
| "model": "Camry", |
| "price": "23500", |
| "year": "2017" |
| } |
| ] |
| } |
+-------------------------------------------+
半構造化データの走査¶
VARIANT 列名と任意の第1レベル要素の間にコロン : を挿入します。 <列>:<第1レベル要素>。
注釈
次の例では、クエリ出力は VARCHARではなく VARIANTであるため、クエリ出力は二重引用符で囲まれています。（VARIANT 値は文字列ではありません。 VARIANT 値は文字列を 含みます。）演算子 : および後続の . および [] は、常に文字列を含む VARIANT 値を返します。
たとえば、すべてのディーラー名のリストを取得します。
SELECT src:dealership FROM car_sales;
+--------------------------+
| SRC:DEALERSHIP |
|--------------------------|
| "Valley View Auto Sales" |
| "Tindel Toyota" |
+--------------------------+
JSON オブジェクトの要素にアクセスするには、次の2つの方法があります。
重要
使用する表記法に関係なく、列名では大文字と小文字が区別されませんが、要素名では大文字と小文字が区別されます。例えば、次のリストでは、最初の2つのパスは同等ですが、3番目のパスは同等ではありません。
src：salesperson.name
SRC:salesperson.name
SRC:Salesperson.Name
ドット表記¶
ドット表記を使用して、JSON オブジェクトのパスを走査します。 <列>:<第1レベル要素>.<第2レベル要素>.<第3レベル要素>。オプションで、要素名を二重引用符で囲みます。 <列>:"<第1レベル要素>"."<第2レベル要素>"."<第3レベル要素>"。
注釈
JSON キー（要素名）のルールは、Snowflake SQL 識別子のルールとは異なります。
Snowflake SQL 識別子のルールの詳細については、 識別子の要件 をご参照ください。
JSON キーの詳細については、http://json.orgの「文字列」の説明をご参照ください。
要素名がSnowflake SQL 識別子ルールに準拠していない場合、例えば、スペースが含まれている場合は、名前を二重引用符で囲む 必要があります 。以下は、二重引用符で囲まないと有効なSnowflake識別子名にならない、有効な JSON 要素名の例です（すべてが上記のcar_salesの例ではありません）。
-- This contains a blank.
SELECT src:"company name" FROM partners;
-- This does not start with a letter or underscore.
SELECT zipcode_info:"94987" FROM addresses;
-- This contains characters that are not letters, digits, or underscores, and
-- it does not start with a letter or underscore.
SELECT measurements:"#sPerSquareInch" FROM english_metrics;
車を販売したすべての営業担当者の名前を取得します。
SELECT src:salesperson.name FROM car_sales;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------+
かっこ表記¶
または、かっこ表記を使用して、オブジェクトのパスを走査します。 <列>['<第1レベル要素>']['<第2レベル要素>']。要素名を一重引用符で囲みます。値は文字列として取得されます。
車を販売したすべての営業担当者の名前を取得します。
SELECT src['salesperson']['name'] FROM car_sales;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------------+
繰り返し要素の単一インスタンスの取得¶
配列参照に番号付き述語（0から始まる）を追加して、繰り返し配列内の子要素の特定の番号付きインスタンスを取得します。
繰り返し配列の子要素の すべての インスタンスを取得するには、配列をフラット化する必要があることにご注意ください。このトピックの FLATTEN関数を使用した配列の解析 の例をご参照ください。
各販売の車両の詳細を取得します。
SELECT src:vehicle[0] FROM car_sales;
+-------------------------+
| SRC:VEHICLE[0] |
|-------------------------|
| { |
| "extras": [ |
| "ext warranty", |
| "paint protection" |
| ], |
| "make": "Honda", |
| "model": "Civic", |
| "price": "20275", |
| "year": "2017" |
| } |
| { |
| "extras": [ |
| "ext warranty", |
| "rust proofing", |
| "fabric protection" |
| ], |
| "make": "Toyota", |
| "model": "Camry", |
| "price": "23500", |
| "year": "2017" |
| } |
+-------------------------+
販売された各車の価格を取得します。
SELECT src:vehicle[0].price FROM car_sales;
+----------------------+
| SRC:VEHICLE[0].PRICE |
|----------------------|
| "20275" |
| "23500" |
+----------------------+
値の明示的なキャスト¶
デフォルトでは、VARIANT 列から取得された値は文字列リテラルとして返されます。予期しない結果を回避するには、値を明示的に目的のデータ型にキャストします（:: 表記を使用）。
営業担当者 ID 番号のリストを文字列値として取得します。
SELECT src:salesperson.id::string FROM car_sales;
+----------------------------+
| SRC:SALESPERSON.ID::STRING |
|----------------------------|
| 55 |
| 274 |
+----------------------------+
FLATTEN 関数を使用した配列の解析¶
FLATTEN 関数を使用して配列を解析します。FLATTEN は、 VARIANT、 OBJECT、または ARRAY 列の側面ビューを生成するテーブル関数です。関数は各オブジェクトの行を返し、LATERAL 修飾子はデータをオブジェクトの外部の情報と結合します。
すべての顧客の名前と住所を取得します。VARIANT 出力を文字列値にキャストします。
SELECT
value:name::string as "Customer Name",
value:address::string as "Address"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:customer);
+--------------------+-------------------+
| Customer Name | Address |
|--------------------+-------------------|
| Joyce Ridgely | San Francisco, CA |
| Bradley Greenbloom | New York, NY |
+--------------------+-------------------+
FLATTEN 関数を使用したネストされた配列の解析¶
extras 配列は、サンプルデータの vehicle 配列内にネストされます。
"vehicle" : [
{"make": "Honda", "model": "Civic", "year": "2017", "price": "20275", "extras":["ext warranty", "paint protection"]}
]
2番目の FLATTEN 句を追加して、フラット化された vehicle 配列内の extras 配列をフラット化し、販売された車ごとに購入された「エクストラ」を取得します。
SELECT
vm.value:make::string as make,
vm.value:model::string as model,
ve.value::string as "Extras Purchased"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:vehicle) vm
, LATERAL FLATTEN(INPUT => vm.value:extras) ve;
+--------+-------+-------------------+
| MAKE | MODEL | Extras Purchased |
|--------+-------+-------------------|
| Honda | Civic | ext warranty |
| Honda | Civic | paint protection |
| Toyota | Camry | ext warranty |
| Toyota | Camry | rust proofing |
| Toyota | Camry | fabric protection |
+--------+-------+-------------------+
PARSE_JSON 関数を使用した VARIANT 値としてのテキストの解析¶
PARSE_JSON 関数を使用して、テキストを JSON ドキュメントとして解析します。
入力が NULL の場合は、出力も NULL になります。ただし、入力文字列が null の場合、VARIANT null 値として解釈されます。つまり、結果は SQL NULL ではなく、半構造化形式でnull値を表すために使用される実際の値です。
例については、このトピック内の 例で使用されるサンプルデータ をご参照ください。
GET 関数を使用した値の抽出¶
GET VARIANT、OBJECT、または ARRAY の値を最初の引数として受け入れ、2番目の引数として提供されたパス内の要素の VARIANT 値を抽出します。
GET および ARRAY_SIZE 関数を使用して、VARIANT 列の各配列の最後の要素を計算して抽出します。ARRAY_SIZE は入力配列のサイズを返します。
注釈
この例は、このトピックの他の場所で使用されている car_sales テーブルとは異なります。
CREATE OR replace TABLE colors (v variant);
INSERT INTO
colors
SELECT
parse_json(column1) AS v
FROM
VALUES
('[{r:255,g:12,b:0},{r:0,g:255,b:0},{r:0,g:0,b:255}]'),
('[{c:0,m:1,y:1,k:0},{c:1,m:0,y:1,k:0},{c:1,m:1,y:0,k:0}]')
v;
SELECT *, GET(v, ARRAY_SIZE(v)-1) FROM colors;
+---------------+-------------------------+
| V | GET(V, ARRAY_SIZE(V)-1) |
|---------------+-------------------------|
| [ | { |
| { | "b": 255, |
| "b": 0, | "g": 0, |
| "g": 12, | "r": 0 |
| "r": 255 | } |
| }, | |
| { | |
| "b": 0, | |
| "g": 255, | |
| "r": 0 | |
| }, | |
| { | |
| "b": 255, | |
| "g": 0, | |
| "r": 0 | |
| } | |
| ] | |
| [ | { |
| { | "c": 1, |
| "c": 0, | "k": 0, |
| "k": 0, | "m": 1, |
| "m": 1, | "y": 0 |
| "y": 1 | } |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 0, | |
| "y": 1 | |
| }, | |
| { | |
| "c": 1, | |
| "k": 0, | |
| "m": 1, | |
| "y": 0 | |
| } | |
| ] | |
+---------------+-------------------------+
GET_PATH 関数を使用したパスによる値の抽出¶
GET_PATH 、 : 関数を使用して、VARIANT 列から値を抽出します。この関数は GET のバリエーションで、パス名を使用して値を抽出するために使用されます。GET_PATH は、 GET の関数のチェーンと同等です。
各顧客が購入した車のメーカーを取得します。
SELECT GET_PATH(src, 'vehicle[0]:make') FROM car_sales;
+----------------------------------+
| GET_PATH(SRC, 'VEHICLE[0]:MAKE') |
|----------------------------------|
| "Honda" |
| "Toyota" |
+----------------------------------+
半構造化データの走査 では、VARIANT 列の要素を取得するために使用されるパス構文について説明します。構文は、GET または GET_PATH 、 : 関数の省略形です。パス構文とは異なり、これらの関数は不規則なパスまたはパス要素を処理できます。
次のクエリは同じ結果を生成します。
SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;
SELECT src:vehicle[0].make FROM car_sales;
ステージングされたデータファイルからの配列の直接解析¶
contacts.json.gz という名前のステージングされたファイルに、次のデータが含まれていると想定します。
{
"root": [
{
"employees": [
{
"firstName": "Anna",
"lastName": "Smith"
},
{
"firstName": "Peter",
"lastName": "Jones"
}
]
}
]
}
また、 my_json_format という名前のファイル形式の定義に、 TYPE=JSON が含まれると想定します。
ステージングされたファイルにある最初の従業員の名前をクエリします。この例では、ファイルは customers テーブルステージにありますが、任意の内部（つまり、Snowflake）または外部ステージに配置することもできます。
SELECT 'The First Employee Record is '||
S.$1:root[0].employees[0].firstName||
' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;
+----------------------------------------------+
| 'THE FIRST EMPLOYEE RECORD IS '|| |
| S.$1:ROOT[0].EMPLOYEES[0].FIRSTNAME|| |
| ' '||S.$1:ROOT[0].EMPLOYEES[0].LASTNAME |
|----------------------------------------------|
| The First Employee Record is Anna Smith |
+----------------------------------------------+