半構造化データのクエリ¶
このトピックでは、特別な演算子と関数を使用して、 VARIANT に格納されている複雑な階層データをクエリする方法について説明します。
（ARRAYs と OBJECTs から値を抽出する方法の簡単な例については、 インデックスまたはスライスによる ARRAY の要素へのアクセス と キーによる OBJECT の要素へのアクセス をご参照ください。）
通常、階層データは、サポートされている次のデータ形式のいずれかから VARIANT にインポートされています。
JSON
Avro
ORC
Parquet
（XML データ（例: XML データ形式で発生し、 PARSE_XML を呼び出すことによって OBJECT に変換されたデータ）のクエリについては、 XMLGET をご参照ください。）
Tip
検索最適化サービスを利用して、クエリのパフォーマンスを向上させることができます。詳細については、 検索最適化サービス をご参照ください。
このトピックの内容:
例で使用されるサンプルデータ¶
特に断りのない限り、このトピックの例では、 src という名前の単一の VARIANT 列を含む car_sales という名前のテーブルを参照しています。この VARIANT には、ネストされた ARRAYs と OBJECTs が含まれています。
テーブルを作成してロードします。
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
ORDER BY 1;
+--------------------------+
| SRC:DEALERSHIP |
|--------------------------|
| "Tindel Toyota" |
| "Valley View Auto Sales" |
+--------------------------+
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
ORDER BY 1;
+----------------------+
| SRC:SALESPERSON.NAME |
|----------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------+
かっこ表記¶
または、かっこ表記を使用して、オブジェクトのパスを走査します。 <列>['<第1レベル要素>']['<第2レベル要素>']。要素名を一重引用符で囲みます。値は文字列として取得されます。
車を販売したすべての営業担当者の名前を取得します。
SELECT src['salesperson']['name']
FROM car_sales
ORDER BY 1;
+----------------------------+
| SRC['SALESPERSON']['NAME'] |
|----------------------------|
| "Frank Beasley" |
| "Greg Northrup" |
+----------------------------+
繰り返し要素の単一インスタンスの取得¶
配列参照に番号付き述語（0から始まる）を追加して、繰り返し配列内の子要素の特定の番号付きインスタンスを取得します。
繰り返し配列の子要素の すべての インスタンスを取得するには、配列をフラット化する必要があることにご注意ください。このトピックの FLATTEN関数を使用した配列の解析 の例をご参照ください。
各販売の車両の詳細を取得します。
SELECT src:customer[0].name, src:vehicle[0]
FROM car_sales
ORDER BY 1;
+----------------------+-------------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0] |
|----------------------+-------------------------|
| "Bradley Greenbloom" | { |
| | "extras": [ |
| | "ext warranty", |
| | "rust proofing", |
| | "fabric protection" |
| | ], |
| | "make": "Toyota", |
| | "model": "Camry", |
| | "price": "23500", |
| | "year": "2017" |
| | } |
| "Joyce Ridgely" | { |
| | "extras": [ |
| | "ext warranty", |
| | "paint protection" |
| | ], |
| | "make": "Honda", |
| | "model": "Civic", |
| | "price": "20275", |
| | "year": "2017" |
| | } |
+----------------------+-------------------------+
販売された各車の価格を取得します。
SELECT src:customer[0].name, src:vehicle[0].price
FROM car_sales
ORDER BY 1;
+----------------------+----------------------+
| SRC:CUSTOMER[0].NAME | SRC:VEHICLE[0].PRICE |
|----------------------+----------------------|
| "Bradley Greenbloom" | "23500" |
| "Joyce Ridgely" | "20275" |
+----------------------+----------------------+
値の明示的なキャスト¶
VARIANT から値を抽出する場合は、値を目的のデータ型に明示的にキャストできます。たとえば、価格を数値として抽出し、それらに対して計算を実行できます。
SELECT src:vehicle[0].price::NUMBER * 0.10 AS tax
FROM car_sales
ORDER BY tax;
+--------+
| TAX |
|--------|
| 2027.5 |
| 2350.0 |
+--------+
デフォルトでは、 VARCHARs、 DATEs、 TIMEs、および TIMESTAMPs が VARIANT 列から取得される場合、値は二重引用符で囲まれます。値を明示的にキャストすると、二重引用符を削除できます。例:
SELECT src:dealership, src:dealership::VARCHAR
FROM car_sales
ORDER BY 2;
+--------------------------+-------------------------+
| SRC:DEALERSHIP | SRC:DEALERSHIP::VARCHAR |
|--------------------------+-------------------------|
| "Tindel Toyota" | Tindel Toyota |
| "Valley View Auto Sales" | Valley View Auto Sales |
+--------------------------+-------------------------+
VARIANT 値のキャストの詳細については、 VARIANT での値の使用 をご参照ください。
一般的なキャストの詳細については、 データ型の変換 をご参照ください。
Using FLATTEN to Filter the Results in a WHERE Clause¶
The FLATTEN function explodes nested values into separate columns. You can use the function to filter query results in a WHERE clause.
The following example returns key-value pairs that match a WHERE clause and displays them in separate columns:
CREATE TABLE pets (v variant);
INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');
SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';
+-------------------------+---------+--------------+
| V | KEY | VALUE |
|-------------------------+---------+--------------|
| { | species | "dog" |
| "is_dog": "true", | | |
| "name": "Fido", | | |
| "species": "dog" | | |
| } | | |
| { | name | "dog terror" |
| "is_dog": "false", | | |
| "name": "dog terror", | | |
| "species": "cat" | | |
| } | | |
+-------------------------+---------+--------------+
Using FLATTEN to List Distinct Key Names¶
When working with unfamiliar semi-structured data, you might not know the key names in an OBJECT. You can use the FLATTEN function with the RECURSIVE argument to return the list of distinct key names in all nested elements in an OBJECT:
SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
TYPEOF(f.value) AS "Type",
COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;
The REGEXP_REPLACE function removes the array index values (e.g. [0]) and replaces them with brackets ([]) to group array elements.
For example:
{"a": 1, "b": 2, "special" : "data"} <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"} <----row 2 of VARIANT column
Output from query:
+---------+---------+-------+
| Path | Type | Count |
|---------+---------+-------|
| a | INTEGER | 1 |
| b | INTEGER | 1 |
| c | INTEGER | 1 |
| d | INTEGER | 1 |
| normal | VARCHAR | 1 |
| special | VARCHAR | 1 |
+---------+---------+-------+
Using FLATTEN to List Paths in an OBJECT¶
Related to Using FLATTEN to List Distinct Key Names, you can use the FLATTEN function with the RECURSIVE argument to retrieve all keys and paths in an OBJECT.
The following query returns keys, paths, and values (including VARIANT "null" values) for all data types stored in a VARIANT column. The code assumes that the VARIANT column contains an OBJECT in each row.
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.index,
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;
The following query is similar to the first query, but excludes nested OBJECTs and ARRAYs:
SELECT
t.<variant_column>,
f.seq,
f.key,
f.path,
REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
TYPEOF(f.value) AS "Type",
f.value AS "Current Level Value",
f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');
The queries return the following values:
- <variant_column>
OBJECT stored as a row in the VARIANT column.
- Seq
Unique sequence number associated with the data in the row.
- Key
String associated with a value in the data structure.
- Path
Path to the element within the data structure.
- Level
Level of the key-value pair within the data structure.
- Type
Data type for the value.
- Index
Index of the element in the data structure. Applies to ARRAY values only; otherwise NULL.
- Current Level Value
Value at the current level in the data structure.
- Above Level Value
Value one level higher in the data structure.
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
ORDER BY make, model, "Extras Purchased";
+--------+-------+-------------------+
| MAKE | MODEL | Extras Purchased |
|--------+-------+-------------------|
| Honda | Civic | ext warranty |
| Honda | Civic | paint protection |
| Toyota | Camry | ext warranty |
| Toyota | Camry | fabric protection |
| Toyota | Camry | rust proofing |
+--------+-------+-------------------+
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 |
+----------------------------------------------+