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

Top