JSON Table Examples

For example, the SQL for your entire view might resemble one or more of the following.

View with a Literal JSON Table

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

View with a JSON Table and a Self-Join

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

View with JSON Table and Inner Join

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