JSONテーブルの例
たとえば、ビュー全体のSQLが次の1つまたはいくつかのようになります。
リテラルJSONテーブルを含む表示
SELECT
columnName,
columnValue
FROM JSON_TABLE('{
"company": {"department": [
{ "DepartmentID": 1, "DepartmentName" : "Sales" },
{ "DepartmentID": 2, "DepartmentName" : "Project" },
{ "DepartmentID": 3, "DepartmentName" : "Market" },
{ "DepartmentID": 4, "DepartmentName" : "HR人力" },
{ "DepartmentID": 5, "DepartmentName" : "Service" },
{ "DepartmentID": 6, "DepartmentName" : "Advertisement" }]
}}',
'$.company.department'
COLUMNS(columnName VARCHAR(20) PATH KEY,columnValue VARCHAR(100) PATH VALUE)) JT
JSONテーブルと自己結合を含む表示
SELECT
two_references.customerId, two_references_1.price
FROM
/shared/myViews/json/dynamic/reference_list/two_references two_references INNER JOIN
/shared/myViews/json/dynamic/reference_list/two_references two_references_1
ON two_references.customerId = two_references_1.customerId
JSONテーブルと内部結合を含む表示
SELECT
literal_json_table_B.employeeName,
literal_json_table_A.departmentID,
literal_json_table_A.departmentName
FROM /shared/jsontable/literal_json_table_B inner JOIN
/shared/jsontable/literal_json_table_A
ON literal_json_table_B.departmentID = literal_json_table_A.departmentID
order by literal_json_table_B.employeeName