Aviato DSL V1

About

The Aviato DSL is a custom query language inspired by Elasticsearch based on JSON that allows the usage of complex filters easily.

DSL Structure

All the following attributes are placed at the top level of the DSL. All fields are required unless otherwise stated

AttributeTypeDescription
offsetintegerA required integer with a max value of 10,000 defining how many items the database should skip before it returns results.
limitintegerA required integer with a max value of 250 defining how many items the database should return per “page.”
sortArray<object>An array of objects defining the order of the returned results. The database will attempt to use the first defined sort, and break ties with the following sorts. Each object in the sort array should have a singular key, which defines the column that the sort should be performed on. This can be empty. In text searches, use the score sort to return most relevant results
nameQueryOptional stringAn optional string that will search the given table using the value inside of nameQuery using full text search on the name or fullName columns. If the score sort is used alongside this field, results will be sorted by textual relevancy, and ties will be broken by ai relevancy.
filtersDSL FiltersAn optional array of objects that can be used to perform complex cross-table filters.

Filter Object

Only one of the following fields can be used per filter object

Attribute NameTypeDescription
ANDArray<FilterObject>[Optional] Used to perform “AND” queries. All filter objects inside this must evaluate to true in order for the parent filter object to evaluate to true.
ORArray<FilterObject>[Optional] Used to perform “OR” queries. One or more filter objects inside this must evaluate to true in order for the parent filter object to evaluate to true
{columnPath}EvaluationObject[Optional] Defines the evaluation object used for comparisons. The attribute name is a “path” to the column that can cross tables using dot notation.

Column Paths

This can reference three things: scalar fields, relational fields, and function calls

Scalar Fields: These are simple scalar fields that exist on the table being queried specified in the “collections” attribute. They must exist as a scalar field in the Prisma schema

Relational Fields: These are relational fields, with no cap on the amount of relations. Each relation is separated by dot notation. The last specified column must be a scalar column. You do NOT navigate by specifying the model name, but instead by the “virtual column name.” Ex: If you’re querying the Person table, and want to filter on the WorkExperienceLink table, the constructed column path would be workExperienceList.startDate and NOT WorkExperienceList.startDate. For example, to filter for companies with a specific founder, use companyFoundingLink.person.id

Function Calls: You can call custom pre-approved functions with inputs by simply calling the function like normal. Ex you would pass vesting_to_total_vested() . To pass custom arguments in, simply pass them in as how you would speciffy scalar/relational fields: vesting_to_total_vested(experienceList.startDate, experienceList.company.vestingScheduleList[1]['schedule'])

  • Note that you can only use non-aggregate functions in filters. However, you can use aggregate functions in sort. Valid function calls include:

    1{
    2	vesting_to_total_vested: {
    3			isAggregate: false,
    4		},
    5		count: {
    6			isAggregate: true,
    7		},
    8		max_timestamp_from_json: {
    9			isAggregate: false,
    10		},
    11		get_lexicographically_greatest_element: {
    12			isAggregate: false,
    13		},
    14		get_latest_position: {
    15			isAggregate: false,
    16		},
    17}

JSON Queries: You might find yourself wanting to query a JSON column’s subattributes. You can’t use dots (otherwise the DSL parser will think you’re trying to make a relational query). Instead, use square brackets. This can be used in tandem with the dots for scalar fields. Ex: experienceList.company.vestingScheduleList[1]['schedule']. Single quotes are used for JSON sub attribute names, numbers without quotes are used for specifying a specific index in a JSON array.

Evaluation Object

Attribute NameTypeDescription
operationenumThe equality operator. Supported values: eq lt lte gt gte noteq fts in notin geowithin textcontains
valuestring or string[] or number or number[] or DateThe value to compare the column against using the equality operator
quantifierOptional: some or noneIf not supplied, defaults to some for maximum performance. Be careful when using none, as this will hurt performance. This attribute describes the number of rows that need to match the operation when we encounter a one-to-many relationship. If specifying a function, only the some quantifier is supported.
  • The eq noteq lt lte gt and gte fields support strings ,numbers, and Dates
  • The fts field supports strings and does full text filtering (not ranking)
  • The in notin fields support arrays of numbers or strings
  • The geowithin field supports arrays of string and does a filter on the specified bounding box. This only supports certain coordinate type fields: ["(latitude, longitude)", "(latitude, longitude)"] Where the first set of coordinates is the upper left coordinate of the bounding box, and the second set of coordinates is the lower right coordinate.
    • Ex ["(38.532667, -123.237130)", "(37.019316, -120.397669)"]
  • The textcontains field supports only strings.

Sort Object Example

1{
2	"myColumn": {
3		"order": "desc"
4	}
5}

The sort attribute also supports all column paths that the filters attribute supports. This includes sorting by relations, sorting by functions, including aggregate functions like COUNT()

1{
2	"COUNT(experienceList.id)": {
3		order: "desc"
4	},
5},
Built with