例: JSON_PUBLISH

Teradata® Database JSONデータ型

prodname
Teradata Database
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1150-162K-JPN

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で実行されるようになります。