English

Metavault API - Get environment database table data (with hard rules)

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

JSON
{
  "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 {{column_name}} (replacing column_name with the actual name of the column) will be replaced by the name of the column.


columns[0].orderBy

string

No

asc will sort rows in ascending order of values in this column, desc in descending order and null will not sort them.


columns[0].filterOperator

string

No

If both this and filterValue are set, only rows validating the filter expression will be returned.

One of: eq,lt,lte,gt,gte,contains,startWith,endWith

The comparison is: columnValue filterOperator filterValue


columns[0].filterValue

string

No

See filterOperator

the “value“ part of the comparison.


Response

Success response (200 OK)

JSON
{
  "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 index parameter given to the request.

total

integer

The total amount of rows that match the filters (if any).

maxResults

integer

The limit parameter given to the request. The name is different for historical reasons.

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 thehardRuleDefinition is set and is valid, this contains the column values after hard rule transformation. Same sorting is applied as in data

values.*.hrError

string

If thehardRuleDefinition is set and is invalid, this contains the database error.