Smart Queries¶

Smart Queries are reusable, predefined queries that you create in the Centrali console and execute programmatically via the API or SDK. They allow you to define complex filters, joins, and sorting once, then run them with dynamic parameters at runtime.

Overview¶

Smart Queries support: - Dynamic Variables - Use {{variableName}} syntax for runtime parameter substitution - Joins - Join data across structures, including joining on record IDs - Complex Filters - Use MongoDB-style operators ($eq, $gt, $in, etc.) - Sorting & Pagination - Define sort order, limit, and skip in the query definition

Creating a Smart Query¶

Smart queries are created in the Centrali console by navigating to a structure and clicking "Smart Queries". You define:

• Name: A unique identifier for the query
• Query Definition: The filter conditions, joins, sorting, and pagination

Query Definition Structure¶

{
  "select": ["title", "status", "createdAt"],
  "where": {
    "status": { "$eq": "active" },
    "priority": { "$gte": 5 }
  },
  "sort": [
    { "field": "createdAt", "direction": "desc" }
  ],
  "limit": 50,
  "skip": 0
}

Variables¶

Variables allow you to create dynamic queries that accept parameters at execution time.

Syntax¶

Use double curly braces to define variables: {{variableName}}

{
  "where": {
    "userId": { "$eq": "{{currentUserId}}" },
    "status": { "$in": ["{{statusFilter}}", "active"] },
    "createdAt": { "$gte": "{{startDate}}" }
  }
}

Variable Naming Rules¶

• Must start with a letter (a-z, A-Z)
• Can contain letters, numbers, and underscores
• Case-sensitive
• Maximum 64 characters

Valid examples: userId, start_date, filterValue1, CamelCase

Invalid examples: 1user, my-var, user.id

Executing with Variables¶

When executing a query that uses variables, you must provide values for all referenced variables.

API Request (POST):

curl -X POST \
  "https://api.centrali.io/workspace/{workspace}/api/v1/smart-queries/slug/{structure}/execute/{queryId}" \
  -H "Authorization: Bearer {token}" \
  -H "Content-Type: application/json" \
  -d '{
    "variables": {
      "currentUserId": "user_123",
      "statusFilter": "pending",
      "startDate": "2024-01-01"
    }
  }'

SDK:

const results = await centrali.smartQueries.execute('orders', 'query-id', {
  variables: {
    currentUserId: 'user_123',
    statusFilter: 'pending',
    startDate: '2024-01-01'
  }
});

Error Handling¶

If required variables are not provided, the API returns a 400 error:

{
  "error": "MISSING_VARIABLE",
  "message": "Missing required variable(s): currentUserId, startDate",
  "requiredVariables": ["currentUserId", "startDate"],
  "providedVariables": ["statusFilter"]
}

Joins¶

Smart Queries support joining data from related structures. Joined fields are returned nested under a _joined object, similar to how _expanded works for reference fields.

Basic Join¶

Join on a field in your structure with a field in another structure:

{
  "select": ["title", "status"],
  "where": {
    "status": { "$eq": "active" }
  },
  "join": {
    "foreignSlug": "categories",
    "localField": "categoryCode",
    "foreignField": "code",
    "select": ["name", "color"]
  }
}

Result structure:

{
  "title": "My Item",
  "status": "active",
  "_joined": {
    "categories": {
      "name": "Electronics",
      "color": "blue"
    }
  }
}

Joining on Record ID¶

Often you'll store a reference to another record's UUID. Use the special _recordId field to join on the actual record ID column instead of a JSONB field.

{
  "select": ["title", "description"],
  "join": {
    "foreignSlug": "authors",
    "localField": "authorId",
    "foreignField": "_recordId",
    "select": ["_recordId", "name", "email"]
  }
}

Result structure:

{
  "title": "My Article",
  "description": "An interesting read",
  "_joined": {
    "authors": {
      "_recordId": "550e8400-e29b-41d4-a716-446655440000",
      "name": "John Doe",
      "email": "john@example.com"
    }
  }
}

This translates to SQL like:

SELECT r1.data->>'title', r1.data->>'description', r2.id, r2.data->>'name', r2.data->>'email'
FROM records r1
JOIN records r2 ON r1.data->>'authorId' = r2.id::text
WHERE r2.recordSlug = 'authors'

_recordId Patterns¶

Filtering on Joined Records (joinWhere)¶

You can filter on fields from the joined table using joinWhere. This applies conditions to the joined records (r2) rather than the main records (r1).

Example: Find product options where the parent product has inventory tracking enabled:

{
  "select": ["sku", "price", "inventory"],
  "where": {
    "merchantId": { "$eq": "{{merchantId}}" }
  },
  "join": {
    "foreignSlug": "products",
    "localField": "productId",
    "foreignField": "_recordId",
    "select": ["title", "inventoryTracking", "lowStockThreshold"]
  },
  "joinWhere": {
    "inventoryTracking": { "$eq": true }
  }
}

This translates to SQL like:

SELECT r1.*, r2.title, r2.inventoryTracking, r2.lowStockThreshold
FROM product_options r1
JOIN products r2 ON r1.productId = r2.id
WHERE r1.merchantId = :merchantId
  AND r2.inventoryTracking = true

joinWhere supports the same operators as where, including $and and $or:

{
  "joinWhere": {
    "$and": [
      { "inventoryTracking": { "$eq": true } },
      { "status": { "$eq": "active" } }
    ]
  }
}

Computed Fields¶

⚠️ Important: Smart Queries can ONLY filter on persisted computed fields. Virtual computed fields will NOT work because they are not stored in the database.

Persisted vs Virtual Computed Fields¶

Why Virtual Computed Fields Don't Work¶

Smart Queries execute SQL directly against the database. Virtual computed fields are only calculated when records are read through the API, so their values don't exist in the database for Smart Queries to filter on.

Using Persisted Computed Fields¶

Required: You MUST set "computedMode": "persisted" for Smart Queries to work with computed fields.

To filter on a computed value like inventory - reserved, create a persisted computed field in your structure:

{
  "name": "availableStock",
  "type": "number",
  "expression": "inventory - reserved",
  "computedMode": "persisted"
}

Then you can filter on it in Smart Queries:

{
  "where": {
    "availableStock": { "$gt": 0 }
  }
}

Important Notes¶

1. Set computedMode: "persisted" - Without this, computed fields default to virtual and won't work with Smart Queries

2. Backfill existing records - When adding a new persisted computed field, existing records won't have the computed value until they're updated. You may need to trigger an update on all records to populate the field.

3. Cross-table computed comparisons not supported - You cannot compare a computed field from one table to a field in another table (e.g., availableStock <= r2.lowStockThreshold). This would require the computed expression feature (not yet available).

Example: Low Stock Alert Query¶

Structure setup (product-options):

{
  "properties": [
    { "name": "inventory", "type": "number" },
    { "name": "reserved", "type": "number" },
    {
      "name": "availableStock",
      "type": "number",
      "expression": "inventory - reserved",
      "computedMode": "persisted"
    }
  ]
}

Smart Query:

{
  "select": ["sku", "inventory", "reserved", "availableStock"],
  "where": {
    "merchantId": { "$eq": "{{merchantId}}" },
    "availableStock": { "$gt": 0 },
    "availableStock": { "$lte": 10 }
  },
  "join": {
    "foreignSlug": "products",
    "localField": "productId",
    "foreignField": "_recordId",
    "select": ["title", "inventoryTracking"]
  },
  "joinWhere": {
    "inventoryTracking": { "$eq": true }
  },
  "sort": [{ "field": "availableStock", "direction": "asc" }]
}

Filter Operators¶

Smart Queries support MongoDB-style operators:

Comparison Operators¶

String Operators¶

Array Operators¶

Logical Operators¶

Combine conditions with $and and $or:

{
  "where": {
    "$and": [
      { "status": { "$eq": "active" } },
      {
        "$or": [
          { "priority": { "$gte": 8 } },
          { "flagged": { "$eq": true } }
        ]
      }
    ]
  }
}

SDK Reference¶

List Smart Queries¶

// List all in workspace
const all = await centrali.smartQueries.listAll();

// List for a specific structure
const queries = await centrali.smartQueries.list('orders');

Get Smart Query¶

// By ID
const query = await centrali.smartQueries.get('orders', 'query-uuid');

// By name
const query = await centrali.smartQueries.getByName('orders', 'Active Orders');

Execute Smart Query¶

// Simple execution
const results = await centrali.smartQueries.execute('orders', 'query-id');

// With variables
const results = await centrali.smartQueries.execute('orders', 'query-id', {
  variables: {
    userId: 'user_123',
    startDate: '2024-01-01'
  }
});

// Accessing joined data
const ordersWithProducts = await centrali.smartQueries.execute('order-items', 'items-with-products');
ordersWithProducts.data.forEach(item => {
  console.log('Item:', item.name);
  console.log('Product:', item._joined?.product?.title);
});

Best Practices¶

1. Name queries descriptively - Use names like "Active Orders by Customer" instead of "Query 1"

2. Document your variables - Add comments in the query description about what variables are expected

3. Use _recordId for foreign keys - When storing record UUIDs as references, join using _recordId for correct matching

4. Type casting happens automatically - Centrali casts values based on your structure's field types (number, boolean, datetime)

5. Test before saving - Use the "Test Query" button in the console to verify your query works before saving

Troubleshooting¶

"Missing required variable" error¶

Ensure you're providing all variables used in the query definition. Check the requiredVariables array in the error response.

Join returns no results¶

• Verify the localField contains valid UUIDs that match records in the foreign structure
• When using _recordId, ensure the local field stores the exact UUID (not a formatted version)
• Check that records exist in the foreign structure with the expected values

Type casting errors¶

Ensure your structure's property types match the values you're querying: - Numbers should be queried as numbers, not strings - Booleans should be true/false - Dates should be in ISO format