Creating JSON based query¶
Functions in JSON Query¶
Currently, the following functionalities are supported:
- Select, Join (only inner join), where, order by, from
- Supported operations on columns:
=
(equal to)!=
(not equal to)<>
(not equal to)>
(greater than)<
(less than)>=
(greater than equal to),<=
(less than equal to),- like
- Supported operations between expressions: AND , OR
Currently, the following functionalities are not supported:
- IN, NOT IN, BETWEEN, LIMIT, MIN, MAX, SUM, AVG, Any
- COUNT, Distinct
- NOT
- IS NULL
- Alias
- Left join, right join, full join, outer join, exists, self-join
- GROUP BY, HAVING
- union, subquery, all, intersect
- Insert into, select top, select *
Getting Started¶
Fetching schema details and Semantic models¶
This section describes how to get the necessary information from SDI to create queries.
Retrieving Schemas to write physical queries¶
The input depends upon the schemas to be considered for fetching the data.
- Divide the input data using SQL keywords
- Form a json query
Use the following endpoint:
1 | POST api/sdi/v3/searchSchemas |
Define the following header:
1 2 | Authorization: {Bearer Token} Content-Type: application/json |
Request example¶
1 2 3 4 5 6 | { "schemas": [ { "schemaName": "nhtsa_make" }] } |
Response example¶
Response
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | { "id": "5e4c461c577c210e17e64357", "originalFileNames": [ "vehiclemake.csv" ], "schemaName": "nhtsa_make", "schemaDescription": "", "dataRegistryId": "C159F0FFED3B787C283AC467424E40E1", "schemaVersion": 0, "latestSchemaVersion": 1, "createdAt": 1582056988262, "lastUpdated": 1582056988286, "schema": { "def": { "dataType": "string", "customTypes": [ "us_state", "partnumber1581984706", "partnumber1", "partnumber1582056922" ] }, "id": { "dataType": "integer" } }, "storageLocation": [ { "locationType": "s3", "location": "s3://parquet-file-storage-dev/ediint2/nhtsa_make", "dataType": "parquet" } ], "metadata": { "versionSuffix": "v", "checksum": "2F9A11BA607CC9E4EA9A90AD23A3BAB2", "pointsToVersion": 1 }, "dataFolderSize": "" } |
Retrieving semantic models to create semantic queries¶
In case of semantic query, you can call retrieve semantic model by GET method of ontology id. Use the following endpoint:
1 | GET api/sdi/v3/ontologies/{id} |
Response example¶
Response
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | { "id": "0C3D4F8B504D50DDF5C9F3511E715580", "ontologyName": "nhtsa_operations", "createdDate": "2019-10-17T12:36:36.129Z", "updatedDate": "2019-10-17T12:36:36.129Z", "classes": [ { "name": "Make", "description": "Make Class" }, { "name": "Vehicle", "description": "Vehicle Class" } ], "schemas": [ { "name": "nhtsa_make", "description": "nhtsa_make schema" }, { "name": "nhtsa_vehicle", "description": "nhtsa_vehicle schema" } ], "classProperties": [ { "name": "MakeId", "description": "MakeId Property of class Make", "datatype": "int", "parentClass": { "name": "Make" } }, { "name": "MakeName", "description": "MakeName Property of class Make", "datatype": "string", "parentClass": { "name": "Make" } }, { "name": "Vin", "description": "Vin Property of class Vehicle", "datatype": "string", "parentClass": { "name": "Vehicle" } }, { "name": "CaseYear", "description": "CaseYear Property of class Vehicle", "datatype": "string", "parentClass": { "name": "Vehicle" } }, { "name": "ModelYear", "description": "ModelYear Property of class Vehicle", "datatype": "string", "parentClass": { "name": "Vehicle" } }, { "name": "MakeId", "description": "MakeId Property of class Vehicle", "datatype": "string", "parentClass": { "name": "Vehicle" } } ], "schemaProperties": [ { "name": "id", "description": "id Property of schema nhtsa_make", "parentSchema": { "name": "nhtsa_make" }, "datatype": "String" }, { "name": "def", "description": "def of schema nhtsa_make", "parentSchema": { "name": "nhtsa_make" }, "datatype": "string" }, { "name": "vin", "description": "vin of schema nhtsa_vehicle", "parentSchema": { "name": "nhtsa_vehicle" }, "datatype": "string" }, { "name": "caseyear", "description": "caseyear Property of schema nhtsa_vehicle", "parentSchema": { "name": "nhtsa_vehicle" }, "datatype": "long" }, { "name": "modelyr", "description": "modelyr of schema nhtsa_vehicle", "parentSchema": { "name": "nhtsa_vehicle" }, "datatype": "string" }, { "name": "make", "description": "make of schema nhtsa_vehicle", "parentSchema": { "name": "nhtsa_vehicle" }, "datatype": "string" } ], "mappings": [ { "name": "mapping1", "description": "default mapping between schema and class property, this", "keyMapping": true, "functionalMapping": false, "classProperty": { "name": "MakeId", "parentClass": { "name": "Make" } }, "schemaProperties": [ { "name": "id", "parentSchema": { "name": "nhtsa_make" } } ] }, { "name": "mapping2", "description": "default mapping between schema and class property, this", "keyMapping": true, "functionalMapping": false, "classProperty": { "name": "MakeName", "parentClass": { "name": "Make" } }, "schemaProperties": [ { "name": "def", "parentSchema": { "name": "nhtsa_make" } } ] }, { "name": "mapping3", "description": "default mapping between schema and class property, this", "keyMapping": true, "functionalMapping": false, "classProperty": { "name": "Vin", "parentClass": { "name": "Vehicle" } }, "schemaProperties": [ { "name": "vin", "parentSchema": { "name": "nhtsa_vehicle" } } ] }, { "name": "mapping4", "description": "default mapping between schema and class property, this", "keyMapping": true, "functionalMapping": false, "classProperty": { "name": "CaseYear", "parentClass": { "name": "Vehicle" } }, "schemaProperties": [ { "name": "caseyear", "parentSchema": { "name": "nhtsa_vehicle" } } ] }, { "name": "mapping5", "description": "default mapping between schema and class property, this", "keyMapping": true, "functionalMapping": false, "classProperty": { "name": "ModelYear", "parentClass": { "name": "Vehicle" } }, "schemaProperties": [ { "name": "modelyr", "parentSchema": { "name": "nhtsa_vehicle" } } ] }, { "name": "mapping6", "description": "default mapping between schema and class property, this", "keyMapping": true, "functionalMapping": false, "classProperty": { "name": "MakeId", "parentClass": { "name": "Vehicle" } }, "schemaProperties": [ { "name": "make", "parentSchema": { "name": "nhtsa_vehicle" } } ] } ] } |
Semantic model example¶
The model below consists of nhtsa_operations
namespace that has two classes Make
and Vehicle
with ‘scope’ edge between the classes and namespace. Make
class has two properties MakeName
and MakeId
denoted by propertyOf
edge. Vehicle
class has four properties ModelYear
, Vin
, CaseYear
, MakeId
.
The class properties have mappings with the schema properties. The model also contains two schemas nhtsa_make
and nhtsa_vehicle
along with their columns def
, id
and modelyr
, vin
, caseyear
, make
respectively.
Creating JSON query¶
Select attributes from a schema¶
Query: Select veh_no, p_crash1, p_crash2, p_crash3, systemdate from vehicle_csv
Distribution according to SQL keywords:
1 2 3 4 5 6 7 8 | select vehicle_csv.veh_no vehicle_csv.p_crash1 vehicle_csv.p_crash2 vehicle_csv.p_crash3 vehicle_csv.systemdate from vehicle_csv |
Query in json format
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | { "description": "", "name": "Sample query 1", "statement": { "from": [ "\"vehicle_csv\"" ], "selectAttrs": [ "\"vehicle_csv\".\"veh_no\"", "\"vehicle_csv\".\"p_crash1\"", "\"vehicle_csv\".\"p_crash2\"", "\"vehicle_csv\".\"p_crash3\"", "\"vehicle_csv\".\"systemdate\"" ] } } |
Select few attributes from a schema with join clause on two schemas¶
Query: select reg_stat_desc from state_csv inner join vehicle_csv on state_csv.reg_stat = vehicle_csv.reg_stat
Distribution according to SQL keywords:
1 2 3 4 5 6 7 8 9 10 | select state_csv .reg_stat_desc from state_csv, vehicle_csv JoinType Inner join (joinType: INNER) betweenSchemas state_csv, vehicle_csv OnColumns state_csv.reg_stat = vehicle_csv.reg_stat |
Query in json format
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | { "description": "", "name": "Sample query 2", "statement": { "from": [ "\"vehicle_csv\"", "\"state_csv\"" ], "selectAttrs": [ "\"state_csv\".\"reg_stat_desc\"" ], "joins":[{ "joinType":"INNER", "relationship":[ { "betweenSchemas":[ "\"state_csv\"", "\"vehicle_csv\"" ], "joinCondition":{ "onColumns":[ { "operator":"=", "columns":[ "\"state_csv\".\"reg_stat\"", "\"vehicle_csv\".\"reg_stat\"" ] } ] } } ] }] } } |
Select few attributes from a schema with join on three schemas¶
Query: select makemodel_csv.model_description , makemodel_csv.filename from vehicle_csv inner join makemodel_csv on vehicle_csv.model = makemodel_csv.model inner join state_csv on vehicle_csv.reg_stat = state_csv.reg_stat
Distribution according to SQL keywords:
1 2 3 4 5 6 7 8 9 10 | select makemodel_csv.model_description, makemodel_csv.filename from makemodel_csv, state_csv, vehicle_csv JoinType Inner join (joinType: INNER) betweenSchemas vehicle_csv, makemodel_csv, vehicle_csv, state_csv OnColumns vehicle_csv.model, makemodel_csv.model, vehicle_csv.reg_stat,state_csv.reg_stat |
Query in json format
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | { "description": "", "name": "Sample query 3", "statement": { "from": [ "\"state_csv\"", "\"vehicle_csv\"", "\"makemodel_csv\"" ], "selectAttrs": [ "\"makemodel_csv\".\"model_description\"", "\"makemodel_csv\".\"file_name\"" ], "joins": [ { "joinType": "INNER", "relationship": [ { "betweenSchemas": [ "\"vehicle_csv\"", "\"state_csv\"" ], "joinCondition": { "onColumns": [ { "operator": "=", "columns": [ "\"vehicle_csv\".\"reg_stat\"", "\"state_csv\".\"reg_stat\"" ] } ] } }, { "betweenSchemas": [ "\"vehicle_csv\"", "\"makemodel_csv\"" ], "joinCondition": { "onColumns": [ { "operator": "=", "columns": [ "\"vehicle_csv\".\"model\"", "\"makemodel_csv\".\"model\"" ] } ] } } ] } ] } } |
Select few attributes from a schema where value of one column matches with value of same column present in another schema¶
Query: Select air_bag_csv.air_bag_description from air_bag_csv where air_bag_csv .air_bag = vehicle_csv.air_bag
Distribution according to SQL keywords:
1 2 3 4 5 6 | select air_bag_csv.air_bag_description from air_bag_csv, vehicle_csv where clause vehicle_csv.air_bag = air_bag_csv.air_bag |
Query in json format
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | { "description": "", "name": "Sample query 4", "statement": { "from": [ "\"vehicle_csv\"", "\"airbag_csv\"" ], "selectAttrs": [ "\"airbag_csv\".\"air_bag_description\"" ], "whereClause":{ "operator":"=", "columns":[ "\"vehicle_csv\".\"air_bag\"", "\"airbag_csv\".\"air_bag\"" ] } } } |
Select few attributes from a schema¶
Select few attributes from a schema where the following are applicable:
- value of one column matches with value of same column present in a different schema
- relationship exists between more than two schemas
- value of a column name is equal to some static value
- order by one column name
Query: SELECT makemodel_csv.Make_Name, makemodel_csv.Model_Description, airbag_csv.air_bag_description, vehicle_csv.DT_TM, vehicle_csv.VIN FROM vehicle_csv INNER JOIN makemodel_csv ON vehicle_csv.MAKE=makemodel_csv.MAKE INNER JOIN airbag_csv ON vehicle_csv.AIR_BAG=airbag_csv.AIR_BAG WHERE vehicle_csv.MODEL=makemodel_csv.MODEL and (vehicle_csv.DT_TM =2013 and vehicle_csv.MAKE = 6 and vehicle_csv.MODEL = 14 and vehicle_csv.P_CRASH1 = 1 and vehicle_csv.AIR_BAG = 20) ORDER BY vehicle_csv.VIN asc
Distribution according to SQL keywords
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | select makemodel_csv.Make_Name, makemodel_csv.Model_Description, airbag_csv.air_bag_description, vehicle_csv.DT_TM, vehicle_csv.VIN from vehicle_csv, makemodel_csv, airbag_csv INNER JOIN (joinType: INNER) makemodel_csv ON vehicle_csv.MAKE = makemodel_csv.MAKE INNER JOIN (joinType: INNER) airbag_csv ON vehicle_csv.AIR_BAG = airbag_csv.AIR_BAG WHERE vehicle_csv.MODEL=makemodel_csv.MODEL and (vehicle_csv.DT_TM =2013 (String) and vehicle_csv.MAKE = 6 (int) and vehicle_csv.MODEL = 14 (int) and vehicle_csv.P_CRASH1 = 1 (int) and vehicle_csv.AIR_BAG = 20) (int) ORDER BY vehicle_csv.VIN asc |
As the value of vehicle_csv.DT_TM is compared with a constant value since the data type of vehicle_csv.DT_TM is String, formed json should have its value in single quotes. JSON accepts string values in single quotes.
The below table shows the schema attributes and data types provided by SDI search schema for vehicle_csv.
Parameter | Type |
---|---|
State | int |
st_case | Int |
veh_no | Int |
dt_tm | String |
reg_stat | Int |
Make | Int |
Model | Int |
mak_mod | Int |
Vin | String |
p_crash1 | Int |
p_crash2 | Int |
p_crash3 | Int |
Mfactor | Int |
air_bag[SS(PCS13] | Int |
systemdate | String |
Query in json format
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | { "description": "", "name": "Sample query 5", "statement": { "from": [ "vehicle_csv", "makemodel_csv", "airbag_csv" ], "selectAttrs": [ "makemodel_csv.Make_Name", "makemodel_csv.Model_Description", "airbag_csv.air_bag_description", "vehicle_csv.DT_TM", "vehicle_csv.VIN" ], "joins":[{ "joinType":"INNER", "relationship":[ { "betweenSchemas":[ "vehicle_csv", "makemodel_csv" ], "joinCondition":{ "onColumns":[ { "operator":"=", "columns":[ "vehicle_csv.MAKE", "makemodel_csv.MAKE" ] } ] } }, { "betweenSchemas":[ "vehicle_csv", "airbag_csv" ], "joinCondition":{ "onColumns":[ { "operator":"=", "columns":[ "vehicle_csv.AIR_BAG", "airbag_csv.AIR_BAG" ] } ] } } ] }], "whereClause":{ "operator":"and", "expressions":[ { "operator":"=", "columns":[ "vehicle_csv.MODEL", "makemodel_csv.MODEL" ] }, { "operator":"or", "expressions":[ { "operator":"=", "columns":[ "vehicle_csv.DT_TM" ], "value":"'2013'" }, { "operator":"=", "columns":[ "vehicle_csv.MAKE" ], "value":"6" }, { "operator":"=", "columns":[ "vehicle_csv.MODEL" ], "value":"14" }, { "operator":"=", "columns":[ "vehicle_csv.P_CRASH1" ], "value":"1" }, { "operator":"=", "columns":[ "vehicle_csv.AIR_BAG" ], "value":"20" } ] } ] }, "orderBy":[ "vehicle_csv.VIN:asc" ] } } |
Create a business query to select few attributes¶
For an existing ontology, create a business query to select few attributes from two schemas where:
- there is join between two classes
- value of a property is equal to some static value
Query in json format:
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | { "description": "", "name": "Sample query 6", "isBusinessQuery": true, "ontologyId": "EC3E3C2B0AACF466FDCEEF61162D53A3", "statement": { "from": [ "Vehicle", "Make" ], "selectAttrs": [ "Vehicle.Vin", "Vehicle.CaseYear", "Vehicle.ModelYear", "Vehicle.MakeId", "Make.MakeId", "Make.MakeName" ], "joins": [{ "joinType": "INNER", "relationship": [ { "betweenSchemas": [ "Vehicle", "Make" ], "joinCondition": { "onColumns": [ { "operator": "=", "columns": [ "Vehicle.MakeId", "Make.MakeId" ] } ] } } ] }], "whereClause":{ "operator":"=", "columns": [ "Vehicle.ModelYear" ], "value": "1994" } } } |
Query JSON Example¶
The below sample shows the JSON query example.
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | { "description": "Analysis of vehicle accident", "isBusinessQuery": true, "name": "crashanalysis", "ontologyId": "82F65989D810C9A6CA04C1487A502F82", "statement": { "from": [ "vehicle", "make" ], "joins": [ { "joinType": "INNER", "relationship": [ { "betweenSchemas": [ "vehicle", "make" ], "joinCondition": { "logicalOperators": [ "AND" ], "onColumns": [ { "columns": [ "vehicle.modelnum", "make.make_num" ], "operator": "=" } ] } } ] } ], "orderBy": [ "make.num:asc" ], "selectAttrs": [ "vehicle.vin", "vehicle.brandName", "vehicle.modelnum", "make.model", "make.year", "make_num" ], "whereClause": { "columns": ["vehicle.vin"], "operator": "=", "value": "NHS1024" } } } |
Description of input query JSON¶
The below sample shows the description of input query json.
Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | { "description": {query description}, "isBusinessQuery": {true or false}, "name": {identifier for the query}, "ontologyId": {ontology Id}, "statement": { "from": [{schema name/s}], "joins": [ { "joinType": {type of join}, "relationship": [ { "betweenSchemas": [ {schema name/s} ], "joinCondition": { "logicalOperators": [ {logical operator value} ], "onColumns": [ { "columns": [{column name/s}], "operator": {operator value} } ] } } ] } ], "orderBy": [ "{column name}:asc", "{column name}:desc" ], "selectAttrs": [{column name/s}], "whereClause": { "expressions": [ { "operator": {operator value}, "columns": [ {column names} ] }, { "expressions": [ { "operator": {operator value} "columns": [ {column name} ], "value": {column value} } ] } ], "operator": {logical operator value} } } } |
Parameter | Type |
---|---|
createdDate | The query creation date is in yyyy-MM-dd'T'HH:mm:ss.SSS'Z' format in UTC time : User is not allowed to add this field as it is system generated field |
updatedDate | The query updated date is in yyyy-MM-dd'T'HH:mm:ss.SSS'Z' format in UTC time : User is not allowed to add this field as it is system generated field |
description | A short description about query. This is an Optional field. |
name | Name given to the query: This is a mandatory field |
statement | It contains from (mandatory), selectAttrs(mandatory), joins(optional), whereClause(optional), orderBy(optional) attributes of query |
statement.from | It contains array of schema names. It is mandatory that all schemas used in query should be part of from clause. |
statement.selectAttrs | It contains array of attributes to be shown in result set of query |
statement.joins | It contains joinType (mandatory), relationship (mandatory), and betweenSchemas(mandatory) json objects. |
statement.joins.joinType | Type of join to be performed. Presently, SDI supports only Inner join and to use correct input value is JoinType: “INNER”. |
statement.joins.relationship | It is an array of objects and above mention join type will be applicable on all expression objects of relationship attribute |
statement.joins.relationship.betweenSchemas | This contains array of schema names on which join is to be performed: Currently only two distinct schema names are allowed to be added. |
statement.joins.relationship.joinCondition | It contains logicalOperators(optional), onColumns(mandatory) JSON objects |
statement.joins.relationship.joinCondition.logicalOperators | It contains logical operator to be applied on onColumns objects, if more than one onColumn object then logical operator is mandatory |
statement.joins.relationship.joinCondition.onColumns | It contains operator (mandatory) and columns (mandatory) JSON objects |
statement.joins.relationship.joinCondition.onColumns.operator | Operator value given in this field will be used to perform join operation: logical operators like 'and'/'or' are not allowed as this join operation is to be performed on columns not on two or more expressions. |
statement.joins.relationship.joinCondition.onColumns.columns | User is allowed to add the column from betweenSchemas attribute on which join operator is to applied |
statement.whereClause | This contains whereClause operation details |
statement.whereClause.operator | This contains operator which gets validated as per columns or expressions provided before creation. for columns non logical operators are allowed whereas for expressions logical operators are allowed. |
statement.whereClause.value | Value of the column for which resultset will be filtered. It only supports boolean, String and numbers as type. |
statement.whereClause.columns | It contains an array of column names, on which operator is to applied to get filtered resultset. |
statement.whereClause.expressions | It can contain an array of expressions |
statement.orderBy | Column name on which resultset is expected to be ordered in ascending order:asc/desc |
ontologyId | Id of created ontology. This is a mandatory field if isBusinessQuery = true |
isBusinessQuery | This contains boolean value. Value should be true for business query and false for physical query. |
Any questions left?
Except where otherwise noted, content on this site is licensed under the MindSphere Development License Agreement.