JSON_PUBLISHの例の設定
この簡単なテーブルは、次の例のJSON_PUBLISHの機能を説明するために使用されます。
CREATE TABLE employeeTable( empID INTEGER, empName VARCHAR(100), empDept VARCHAR(100));
INSERT INTO employeeTable(1,'George Smith','Accounting'); INSERT INTO employeeTable(2,'Pauline Kramer','HR'); INSERT INTO employeeTable(3,'Steven Mazzo','Engineering');
JSON_PUBLISHの使用例
テーブル全体を公開し、結果を集計します。
SELECT * FROM JSON_PUBLISH ( ON (SELECT * FROM employeeTable) ) AS jsonData;
data
----------------------------------------------------
[{
"empID": 3,
"empName": "Steven Mazzo",
"empDept": "Engineering"
},
{
"empID": 1,
"empName": "George Smith",
"empDept": "Accounting"
},
{
"empID": 2,
"empName": "Pauline Kramer",
"empDept": "HR"
}]
結果を集計せずに、テーブル全体を公開します。
SELECT * FROM JSON_PUBLISH
(
ON (SELECT * FROM employeeTable)
USING DO_AGGREGATE('N')
) AS jsonData ORDER BY data..empID;
data
----------------------------------------------------
[{
"empID": 1,
"empName": "George Smith",
"empDept": "Accounting"
}]
----------------------------------------------------
[{
"empID": 2,
"empName": "Pauline Kramer",
"empDept": "HR"
}]
----------------------------------------------------
[{
"empID": 3,
"empName": "Steven Mazzo",
"empDept": "Engineering"
}]
----------------------------------------------------
集計せずにテーブル全体を公開し、名前を変更します。
SELECT * FROM JSON_PUBLISH
(
ON (SELECT empID as ID, empName as "Name", empDept as Dept
FROM employeeTable)
USING DO_AGGREGATE('N')
) AS jsonData ORDER BY data.."ID";
data
----------------------------------------------------
[{
"ID": 1,
"Name": "George Smith",
"Dept": "Accounting"
}]
----------------------------------------------------
[{
"ID": 2,
"Name": "Pauline Kramer",
"Dept": "HR"
}]
----------------------------------------------------
[{
"ID": 3,
"Name": "Steven Mazzo",
"Dept": "Engineering"
}]
----------------------------------------------------
テーブル全体を公開し、JSON配列を構築することなく、集計することなく、UNICODE文字セットを使用します。
SELECT * FROM JSON_PUBLISH
(
ON (SELECT * FROM employeeTable)
RETURNS (col1 JSON CHARACTER SET UNICODE)
USING WRITE_ARRAY('N') DO_AGGREGATE('N')
) AS jsonData ORDER BY col1..empID;
col1
---------------------------------------------------------------
{"empID":1,"empName":"George Smith","empDept":"Accounting"}
---------------------------------------------------------------
{"empID":2,"empName":"Pauline Kramer","empDept":"HR"}
---------------------------------------------------------------
{"empID":3,"empName":"Steven Mazzo","empDept":"Engineering"}
---------------------------------------------------------------
JSON_PUBLISHを使用して、複数の行から1つのインスタンスに複数の値を集約することができます。ただしこの集約は、Teradata内のAMPごとにローカルで行なわれます。このため、JSON_PUBLISHを文の中で2回呼び出し、すべての入力値の単一の集約を実行する場合もあります。JSON_PUBLISHをPARTITION BY句なしに1回呼び出す場合は、各AMPは集約された出力の1つの行を生成します。
次のINSERT文では入力データを例のテーブルに追加しており、この点を説明しています。なおこの最初のステップでは、実際の集約はそれが実行されるシステムのアーキテクチャに左右される点に注意してください。
INSERT INTO employeeTable(4,'Jose Hernandez','Engineering'); INSERT INTO employeeTable(5,'Kyle Newman','Engineering'); INSERT INTO employeeTable(6,'Pamela Giles','Sales');
SELECT data FROM JSON_PUBLISH ( ON (SELECT * FROM employeeTable) ) AS jsonData;
data
----------------------------------------------------
[{
"empID": 5,
"empName": "Kyle Newman",
"empDept": "Engineering"
},
{
"empID": 3,
"empName": "Steven Mazzo",
"empDept": "Engineering"
},
{
"empID": 1,
"empName": "George Smith",
"empDept": "Accounting"
},
{
"empID": 2,
"empName": "Pauline Kramer",
"empDept": "HR"
}]
----------------------------------------------------
[{
"empID": 4,
"empName": "Jose Hernandez",
"empDept": "Engineering"
}]
----------------------------------------------------
[{
"empID": 6,
"empName": "Pamela Giles",
"empDept": "Sales"
}]
----------------------------------------------------
すべてのAMPから最終的なUNIONを実行するには、各AMPからのローカルの結果を集約する必要があります。これは、JSON_PUBLISHへの2番目の呼び出しを入れ子にすることで実行できます。JSON_PUBLISHへの内部呼び出しは上述のようにローカル集約を各AMPで実行し、外部呼び出しはそのローカル集約の最終的な集約を実行して、すべての入力値のUNIONを表わす1つの結果行を生成します。
Partition BY句を定数値(例えば1の値)とともに使用して、最終的な集約を1つのAMPで実行します。数値1などの定数値を指定すると、各AMPからローカルで集約された行は同じパーティションに含まれるため、最終的な集約で同じAMPに再配置されます。外部クエリーは以下で"1 as p"として示される次の定数によってパーティション化します。単一の出力行が返されます。
さらに、再帰下降演算子'..'を使用してJSON_PUBLISH内部クエリーの結果名を参照することで、ドット表記を使用して集約結果を参照できます。このクエリーには'*'ワイルドカードを含む配列参照が続き、入力行ごとに1つのレコードを含む1つの配列が取得されます。最後のクエリーは以下のような外観になります。
select data..record[*] FROM JSON_PUBLISH
(
ON (SELECT data as record, 1 as p FROM JSON_PUBLISH
(
ON (SELECT * FROM employeeTable)
)as L
) partition by p
)G;
data..record[*]
----------------------------------------------------
[{
"empID": 5,
"empName": "Kyle Newman",
"empDept": "Engineering"
},
{
"empID": 3,
"empName": "Steven Mazzo",
"empDept": "Engineering"
},
{
"empID": 1,
"empName": "George Smith",
"empDept": "Accounting"
},
{
"empID": 2,
"empName": "Pauline Kramer",
"empDept": "HR"
},
{
"empID": 6,
"empName": "Pamela Giles",
"empDept": "Sales"
},
{
"empID": 4,
"empName": "Jose Hernandez",
"empDept": "Engineering"
}]
なお、定数値によるパーティション化を行なうことでJSON_PUBLISHへの単一呼び出しによってすべての値を集約できますが、この場合、集約を実行するためにすべての行が単一のAMPに再配置され、Teradataの並列処理の長所が活用されなくなります。JSON_PUBLISHへの2回の呼び出しを使用することで、AMPは複数のローカルの集約を並行して実行し、最後の集約のみが単一のAMPで実行されるようになります。