Skip to content

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.

Semantic model example

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?

Ask the community

Except where otherwise noted, content on this site is licensed under the MindSphere Development License Agreement.