Item Query Reference Guide

Note: Operator and parameter reference. For patterns and end-to-end examples, see Advanced Query Guide.

← Back to Documentation Home

This guide provides detailed information about all supported query parameters, wildcards, dynamic variables, and other options for querying items in BAASIX.

Table of Contents

1. Query Parameters
2. Filtering
3. Sorting
4. Pagination
5. Field Selection
6. Search
7. Aggregation
8. Grouping
9. Wildcards
10. Dynamic Variables
11. Relational Queries
12. Geospatial Queries

Query Parameters

Filtering

The filter parameter supports complex conditions using a JSON object.

Operators

• Comparison: eq, ne, gt, gte, lt, lte
• Logical: AND, OR
• Array: in, notIn, arraycontains, arraycontained
• String: like (auto-adds %), iLike (auto-adds %), startsWith, endsWith
• Null: isNull, isNotNull
• Range: between
• Geospatial: within, containsGEO, intersects, dwithin
• Column Comparisons: Use $COL(columnName) to compare against other fields

Column-to-Column Comparisons

Use $COL(columnName) to compare field values against other field values:

# Compare actual vs estimated cost
?filter={"actualCost":{"gt":"$COL(estimatedCost)"}}

# Compare with type casting
?filter={"startTime":{"gt":"$COL(endTime)","cast":"time"}}

# PostgreSQL casting syntax in column reference
?filter={"startTime":{"gt":"$COL(endTime::time)","cast":"time"}}

Examples

1. Simple equality:

   ?filter={"status":{"eq":"active"}}

2. Multiple conditions (AND):

   ?filter={"status":{"eq":"active"},"age":{"gt":30}}

3. Logical OR:

   ?filter={"OR":[{"status":{"eq":"active"}},{"age":{"gt":30}}]}

4. Nested AND/OR:

   ?filter={"AND":[{"OR":[{"status":{"eq":"active"}},{"status":{"eq":"pending"}}]},{"age":{"gt":30}}]}

5. In array:

   ?filter={"status":{"in":["active","pending"]}}

6. String matching (auto-adds % wildcards):

   ?filter={"name":{"like":"John"}}

7. Null check:

   ?filter={"deletedAt":{"isNull":true}}

8. Range:

   ?filter={"createdAt":{"between":["2023-01-01","2023-12-31"]}}

Sorting

The sort parameter accepts a JSON object specifying fields and directions.

Examples

1. Single field ascending:

   ?sort={"name":"asc"}

2. Multiple fields:

   ?sort={"status":"asc","createdAt":"desc"}

3. Sort by distance (for geospatial fields):

   ?sort={"_distance":{"target":[0,0],"column":"location","direction":"ASC"}}

Pagination

Use limit and page parameters for pagination.

Example:

?limit=20&page=2

Field Selection

Use the fields parameter to specify which fields to return.

Examples

1. Select specific fields:

   ?fields=id,name,email

2. Select all fields of a relation:

   ?fields=id,name,posts.*

3. Select specific fields of a relation:

   ?fields=id,name,posts.id,posts.title

4. Deep nesting:

   ?fields=id,name,posts.id,posts.title,posts.author.name

Search

Use search for full-text search and searchFields to specify which fields to search in.

Example:

?search=John&searchFields=name,email

Aggregation

The aggregate parameter allows for complex calculations.

Supported Functions

• count
• sum
• avg
• min
• max

Examples

1. Single aggregation:

   ?aggregate={"totalUsers":{"function":"count","field":"id"}}

2. Multiple aggregations:

   ?aggregate={"totalUsers":{"function":"count","field":"id"},"avgAge":{"function":"avg","field":"age"}}

Grouping

Use groupBy in combination with aggregate for grouped calculations.

Example:

?groupBy=status&aggregate={"userCount":{"function":"count","field":"id"}}

Wildcards

Wildcards are supported in field selection and filtering.

Examples

1. Select all fields:

   ?fields=*

2. Select all fields of a relation:

   ?fields=id,name,posts.*

3. Select all nested fields:

   ?fields=id,name,posts.**

Dynamic Variables

Dynamic variables can be used in filters to reference properties of the authenticated user.

Available Variables

• $CURRENT_USER - Current user ID
• $CURRENT_TENANT - Current tenant ID
• $USER_ROLE - Current user role
• $NOW - Current timestamp

Examples

1. Filter by current user:

   ?filter={"authorId":{"eq":"$CURRENT_USER"}}

2. Filter by current tenant (multi-tenant mode):

   ?filter={"tenant_Id":{"eq":"$CURRENT_TENANT"}}

3. Filter by current timestamp:

   ?filter={"publishedAt":{"lte":"$NOW"}}

Relational Queries

You can query related data using dot notation in filters and field selection.

Examples

1. Filter by related field:

   ?filter={"author.name":{"eq":"John Doe"}}

2. Select fields from related entities:

   ?fields=id,title,author.name,author.email

3. Filter with nested relations:

   ?filter={"posts.comments.author.id":{"eq":"$CURRENT_USER"}}

4. Require related entities:

   ?filter={"comments":{"required":true}}

Geospatial Queries

For fields of type Point, LineString, Polygon, or Geography, you can use geospatial operators.

Operators

• within
• containsGEO
• intersects
• dwithin

Examples

1. Points within a polygon:

   ?filter={"location":{"within":{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}}}

2. Polygon contains point:

   ?filter={"area":{"containsGEO":{"type":"Point","coordinates":[0,0]}}}

3. Intersecting geometries:

   ?filter={"route":{"intersects":{"type":"LineString","coordinates":[[0,0],[1,1]]}}}

4. Distance within:

   ?filter={"location":{"dwithin":{"geometry":{"type":"Point","coordinates":[0,0]},"distance":1000}}}

5. Sort by distance:

   ?sort={"_distance":{"target":[0,0],"column":"location","direction":"ASC"}}

This reference guide covers all the querying capabilities of the BAASIX Item Routes, including advanced filtering, sorting, pagination, field selection, search, aggregation, grouping, wildcards, dynamic variables, relational queries, and geospatial queries. Use these options in combination to create powerful and flexible queries for your data.

Related Documentation

• Item Routes - API endpoints for data operations
• Schema Reference Guide - Data model definitions
• API Routes Reference - Complete list of all API endpoints

← Back to Documentation Home