GetTableDataExtended - POST
|
Path |
/api/projects/{projectId}/environments/{environmentName}/schemas/{schemaName}/tables/{tableName}/query |
|---|---|
|
Http Method |
POST |
Overview
Get the contents of a table, optionally applying transformations (hard rules), filters and sorting on the data. Typically used to retrieve data from the staging tables with hard rules applied.
See: Metavault API - Get environment list for environments.
Required permissions
-
Read access to the specified environment. ENVIRONMENT READ
Request
Path parameters
|
Parameter |
Type |
Required |
Description |
Example |
|---|---|---|---|---|
|
environmentName |
string |
Yes |
The name of the environment. |
“modeling“ |
|
schemaName |
string |
Yes |
The name of the schema. |
“stg“ |
|
tableName |
string |
Yes |
The name of the table |
“employees“ |
Request body
{
"index": 2,
"limit": 8,
"columns": [
{
"name": "name",
"hardRuleDefinition": "LENGTH({{name}})",
"orderBy": "asc",
"filterOperator": "contains",
"filterValue": "e"
},
{
"name": "color",
"hardRuleDefinition": "wrong function"
},
{
"name": "speed",
"hardRuleDefinition": null
}
]
}
Field descriptions
|
Field |
Type |
Required |
Description |
Constraints |
|---|---|---|---|---|
|
index |
integer |
No |
At which position in the full result set to return data. Defaults to 0. |
|
|
limit |
integer |
No |
The maximum amount of values to return. Defaults to 10. |
|
|
columns |
array |
Yes |
A list of JSON objects. The list of columns to return and their parameters. The sorting parameters of every column are applied in the order of the columns. |
|
|
columns[0].name |
string |
Yes |
The name of the column to return the data for. |
|
|
columns[0].hardRuleDefinition |
string |
No |
A hard rule configuration to execute. If this is set, the return values will include the results of this expression for every row. Inside this SQL expression, the token |
|
|
columns[0].orderBy |
string |
No |
|
|
|
columns[0].filterOperator |
string |
No |
If both this and One of: The comparison is: columnValue |
|
|
columns[0].filterValue |
string |
No |
See the “value“ part of the comparison. |
|
Response
Success response (200 OK)
{
"index": 2,
"total": 5,
"maxResults": 8,
"values": {
"name": {
"data": [
"Billie Eilish",
"John Bardeen",
"Kathy Perry"
],
"hrData": [
13,
12,
11
]
},
"color": {
"data": [
"green",
"red",
"blue"
],
"hrError": "Error while executing reader query. Message: [42601: syntax error at or near "AS" POSITION: 27]. Query: [SELECT wrong function AS "HR" FROM "stg"."ss1_dp1_employee" AS r WHERE r."name" LIKE '%e%' ORDER BY r."name" ASC LIMIT 8 OFFSET 2;]"
},
"speed": {
"data": [
"2.56",
"77.31",
"0.012"
]
}
}
}
Response fields
|
Field |
Type |
Description |
|---|---|---|
|
index |
integer |
The |
|
total |
integer |
The total amount of rows that match the filters (if any). |
|
maxResults |
integer |
The |
|
values |
object |
An object that matches the column name with the column data. |
|
values.* |
table columns |
Every entry uses the column name as the key. |
|
values.*.data |
array |
The list of values in the column. To get a full row, retrieve the value at the same index for every column. The JSON type actually used in the object is unspecified, it may match the actual database type, or not. In other words, they all use the same sorting. |
|
values.*.hrData |
array |
If the |
|
values.*.hrError |
string |
If the |