Introduction
This document describes how the "data-search" API works, allowing users to search for and retrieve aggregates from a DAZZM database.
Note on Examples
The examples in this document use a CRM model. This CRM facilitates the management of buying and selling electronic products and includes the following aggregates:
Account: Represents customers and contacts.
Product: Represents the products available for purchase or sale.
Opportunity: Represents various interactions between customers and products.
Queries and Responses
Structure of a Query with fetch
Below is a simple query example that retrieves a list of products (Product) where the name is equal to "Lenovo756":
fetch('https://your_environment.api.octopus-esm.com/prod/data-search', {
method: 'POST',
headers: {
'Content-Type': 'application/json;charset=UTF-8',
'api-key': 'your_token'
},
body: JSON.stringify({
query: {
typename: 'Product',
criteria: [
{
name: {
equal: 'Lenovo756'
}
}
]
}
})
})
.then(response => response.json())
.then(body => console.log(body))
.catch(error => console.error(error));
.png)
A query requires 2 elements, a ‘typename’, which represents the name of the aggregate and a ‘criteria’ which represents the search criterion.
Note 1 : Using an empty criteria will return all possible results for that query.
Note 2 : In order to make an external API call, you need to get an API token, configured in your application.
Server Response Structure
Note that the return value is in JSON format and is always a collection of items (data) and the total number of items (totalCount).
{
"data": [
{
"purchasePrice": 449.99,
"_version": 1,
"unitPrice": 599.99,
"_typename": "Product",
"name": "Lenovo756",
"supplier": {
"id": "58d8d274-0df2-4466-84aa-b3d4b730bdd8"
},
"id": "b816b7b3-490f-4ca3-b7b2-87327d925b52"
}
],
"totalCount": 1
}Comparison Operators
We rely on MongoDb to build our operators. The next section, which specifies the operators used, will include an indication of the relationship to MongoDb in the (MongoDB Compliant) column.
Equal [MongoDB Compliant ($eq)]
Specifies an equality condition. The equal operator matches documents in which the value of a field is equal to the specified value.
Example 1: Find the product with the name “Lenovo756”.
{
"name": {
"equal": "Lenovo756"
}
}Example 2: Find products without a unit price.
{
"unitPrice": {
"equal": null
}
}Example 3: Find products from a specific supplier (the supplier with ID 58d8d274-0df2-4466-84aa-b3d4b730bdd8).
{
"supplier": {
"equal": {
"id": "58d8d274-0df2-4466-84aa-b3d4b730bdd8"
}
}
}notEqual: [Equivalent to MongoDB ($ne)]
The notEqual operator selects documents where the value of the specified field is not equal to the given value. This also includes documents that do not contain the specified field.
Example: Find products that are not supplied by Sony (supplier ID 58d8d274-0df2-4466-84aa-b3d4b730bdd8).
{
"supplier": {
"notEqual": {
"id": "58d8d274-0df2-4466-84aa-b3d4b730bdd8"
}
}
}Example 2: Find products that have a unit price.
{
"UnitPrice": {
"notEqual": null
}
} insensitiveEqual: [Native DAZZM Criterion]
The insensitiveEqual operator matches documents where the value of a field is equal to the specified value, without considering accented letters.
Example: Find the product with the name "lenovo".
{
"name": {
"insensitiveEqual": "lenovo756"
}
}Note : We have a product "Lenovo756" in the database. With this operator, this product will appear in the results.
in: [Equivalent to MongoDB ($in)]
The in operator selects documents where the value of a field matches any of the specified values in the array.
Example: Find products supplied by Sony (supplier ID 58d8d274-0df2-4466-84aa-b3d4b730bdd8) as well as products supplied by Microsoft (supplier ID 038c6b5f-3b4a-412d-a693-4424540d3e95).
{
"supplier": {
"in": [
{
"id": "58d8d274-0df2-4466-84aa-b3d4b730bdd8"
},
{
"id": "038c6b5f-3b4a-412d-a693-4424540d3e95"
}
]
}
}notIn: [Equivalent to MongoDB ($nin)]
The notIn operator selects documents where the field value is not in the specified array, or the specified field does not exist.
Example: Find products that are not supplied by Sony (supplier ID 58d8d274-0df2-4466-84aa-b3d4b730bdd8) or Microsoft (supplier ID 038c6b5f-3b4a-412d-a693-4424540d3e95).
{
"supplier": {
"notIn": [
{
"id": "58d8d274-0df2-4466-84aa-b3d4b730bdd8"
},
{
"id": "038c6b5f-3b4a-412d-a693-4424540d3e95"
}
]
}
}beginsWith: [Native DAZZM Criterion]
The beginsWith operator matches documents where the field value starts with a specified string.
Example: Our product list contains multiple versions of Lenovo. If we want to find all Lenovo products regardless of their versions, we can use beginsWith with "Lenovo".
{
"name": {
"beginsWith": "Lenovo"
}
}contains: [Native DAZZM Criterion]
The contains operator matches documents that contain the specified word within a given field.
Example 1: Find all products in the 5000 series from the product list.
{
"name": {
"contains": "5000"
}
}Example 2: Find all phones in the product list (by searching for "phone").
{
"name": {
"contains": "phone"
}
}containsWords: [Native DAZZM Criterion]
The containsWords operator matches documents where a specified field contains multiple words (separated by spaces).
Example: Find the list of suppliers located on Pont Street.
{
"typename": "Account",
"criteria": [
{
"address": {
"containsWords": "pont"
}
}
]
}This operator also allows searching across multiple fields for a specific word.
Example: Find all users whose first name or last name is "John".
{
"firstName, lastName": {
"containsWords": "John"
}
} lessThan: [Equivalent to MongoDB ($lt)]
The lessThan operator selects documents where the field value is less than (<) the specified value.
Example: Find all products with a unit price lower than $500.
{
"unitPrice": {
"lessThan": 500
}
}lessOrEqualThan: [Equivalent to MongoDB ($lte)]
The lessOrEqualThan operator selects documents where the field value is less than or equal to (<=) the specified value.
Example: Find all products with a unit price of $500 or lower.
{
"unitPrice": {
"lessOrEqualThan": 500
}
}greaterThan : [Conforme à MongoDB ($gt)]
The greaterThan operator selects documents where the field value is greater than (>) the specified value.
Example: Find all products with a unit price greater than $500.
{
"unitPrice": {
"greaterThan": 500
}
}greaterOrEqualThan: [Equivalent to MongoDB ($gte)]
The greaterOrEqualThan operator selects documents where the field value is greater than or equal to (>=) the specified value.
Example: Find all products with a unit price of $500 or higher.
{
"unitPrice": {
"greaterOrEqualThan": 500
}
}dateRangeExpression: [Native DAZZM Criterion]
The dateRangeExpression operator selects documents where the UTCDate field falls within a specified time range using the following operators:
UTCDateRange.Yesterday
UTCDateRange.Tomorrow
UTCDateRange.ThisWeek
UTCDateRange.LastWeek
UTCDateRange.NextWeek
UTCDateRange.ThisMonth
UTCDateRange.LastMonth
UTCDateRange.NextMonth
UTCDateRange.ThisYear
UTCDateRange.LastYear
UTCDateRange.NextYear
UTCDateRange.between(from, to)
UTCDateRange.notBetween(from, to)
UTCDateRange.range(from, 'day'|'week'|'month'|'year')
UTCDateRange.last(number, 'day'|'week'|'month'|'year')
UTCDateRange.next(number, 'day'|'week'|'month'|'year')
UTCDateRange.earlierThan(number, 'day'|'week'|'month'|'year')
UTCDateRange.laterThan(number, 'day'|'week'|'month'|'year')Example (between): Find the list of opportunities where the estimated closing date is between December 1, 2023, and December 31, 2023.
{
"estimatedClosingDate": {
"dateRangeExpression": "UTCDateRange.between('2023-12-01T00:00:00.000Z', '2023-12-31T00:00:00.000Z')"
}
}Example (yesterday): Find the list of opportunities where the estimated closing date was yesterday.
{
"estimatedClosingDate": {
"dateRangeExpression": "UTCDateRange.Yesterday"
}
}Example (laterThan): Find the list of opportunities where the estimated closing date is one week from today.
{
"estimatedClosingDate": {
"dateRangeExpression": "UTCDateRange.laterThan(1, 'week')"
}
}dateTimeRangeExpression: [Native criterion to DAZZM]
Selects documents in which the value of the DateTime field falls within the time range specified using the following operators:
DateTimeRange.ThisMinute
DateTimeRange.LastMinute
DateTimeRange.NextMinute
DateTimeRange.ThisHour
DateTimeRange.LastHour
DateTimeRange.NextHour
DateTimeRange.Today
DateTimeRange.Yesterday
DateTimeRange.Tomorrow
DateTimeRange.ThisWeek
DateTimeRange.LastWeek
DateTimeRange.NextWeek
DateTimeRange.ThisMonth
DateTimeRange.LastMonth
DateTimeRange.NextMonth
DateTimeRange.ThisYear
DateTimeRange.LastYear
DateTimeRange.NextYear
DateTimeRange.between(from, to)
DateTimeRange.notBetween(from, to)
DateTimeRange.range(from, 'day'|'week'|'month'|'year')
DateTimeRange.last(number, 'day'|'week'|'month'|'year')
DateTimeRange.next(number, 'day'|'week'|'month'|'year')
DateTimeRange.earlierThan(number, 'day'|'week'|'month'|'year')
DateTimeRange.laterThan(number, 'day'|'week'|'month'|'year')
Example (between): Find the list of opportunities whose closing date is between 12/01/2023 and 12/31/2023.
{
"closingDate": {
"dateTimeRangeExpression": "DateTimeRange.between('2023-12-01T00:00:00.000-05:00', '2023-12-31T00:00:00.000-05:00')"
}
}Example (yesterday): Find the list of opportunities where the closing date was yesterday.
{
"closingDate": {
"dateTimeRangeExpression": "DateTimeRange.Yesterday"
}
}Example (laterThan): Find the list of opportunities where the closing date is one week from today.
{
"closingDate": {
"dateTimeRangeExpression": "DateTimeRange.laterThan(1, 'week')"
}
}itemMatch: [Equivalent to MongoDB ($elementMatch)]
The itemMatch operator selects documents containing an array field where at least one element meets all specified query criteria.
Example:
Referring to the sample schema in this document, an Account (aggregate) can have multiple contacts (entity collections). If we want to find an account that has a contact matching both of these criteria:
The contact's gender is "MALE".
The contact's title is "Salesperson".
Important: Using the
ANDoperator in the query will not return the correct results.
For example, this incorrect query:
{
"contacts": {
"gender": "MALE",
"title": "Salesperson"
}
}Will be converted to:
{
"$and": [
{
"contacts.gender": { "$eq": "MALE" }
},
{
"contacts.title": { "$eq": "Salesperson" }
}
]
}This translates to:
"Find an account that has a contact with gender MALE and any other contact with the title 'Salesperson'."
This does not meet our requirement.
To ensure both conditions apply to the same contact, we must use itemMatch:
{
"contacts": {
"itemMatch": {
"gender": { "equal": "MALE" },
"title": { "equal": "Salesperson" }
}
}
}This query correctly returns accounts with at least one contact that is both MALE and a Salesperson.
Logical Operators
AND Operator
The AND operator performs a logical AND operation on an array of one or more expressions (<expression1>, <expression2>, etc.), selecting documents that satisfy all conditions.
To perform an AND operation, expressions must be inside the same object, separated by commas.
Example: Find products supplied by Sony (ID: 58d8d274-0df2-4466-84aa-b3d4b730bdd8) AND with a unit price lower than $500.
{
"typename": "Product",
"criteria": [
{
"supplier": {
"equal": {
"id": "58d8d274-0df2-4466-84aa-b3d4b730bdd8"
}
},
"unitPrice": {
"lessThan": 500
}
}
]
}OR Operator
The OR operator performs a logical OR operation on an array of one or more <expressions>, selecting documents that satisfy at least one of the conditions.
To perform an OR operation, expressions must be inside separate objects.
Example: Find products supplied by Sony (ID: 58d8d274-0df2-4466-84aa-b3d4b730bdd8) OR with a unit price greater than $500.
{
"typename": "Product",
"criteria": [
{
"supplier": {
"equal": {
"id": "58d8d274-0df2-4466-84aa-b3d4b730bdd8"
}
}
},
{
"unitPrice": {
"greaterThan": 500
}
}
]
}Sorting, Limits, and Pagination
Sorting by One or More Fields
Sorting can be applied to one or multiple fields using the orderBy operator.
Example 1: Sort products in ascending order by name.
{
"typename": "Product",
"criteria": [],
"orderBy": {
"name": "asc"
}
}Example 2: Sort products in ascending order by name, then in descending order by unit price.
{
"typename": "Product",
"criteria": [],
"orderBy": {
"name": "asc",
"unitPrice": "desc"
}
} Note : The sorting first applies to
name(ascending), then tounitPrice(descending). The order of fields matters..
Changing the Number of Returned Items
To limit the number of results, use first in the query.
Example: Retrieve only the first 10 products.
{
"typename": "Product",
"criteria": [],
"first": 10
}Example: Retrieve the next 10 products using offset.
{
"typename": "Product",
"criteria": [],
"offset": 10,
"first": 10
}Nested Elements
Queries can be performed on nested entities inside aggregates.
Example: Find accounts (aggregate) that have contacts (entity) with gender MALE.
{
"typename": "Account",
"criteria": [
{
"contacts": {
"gender": {
"equal": "MALE"
}
}
}
]
}Using Queries on the DAZZM Platform
This section briefly demonstrates how the same "data-search" API can be used when building applications on the DAZZM platform.
Using the SDK
The SDK integrated into the low-code tool provides access to a "db" object. The "search" method follows the same syntax described in this document.
Example: Search using db.search
db.search({
typename: 'Product',
criteria: [
{
name: {
equal: 'Lenovo756'
}
}
]
});Using Visual Tools
DAZZM's no-code experience allows query creation through a visual tool, which uses the same underlying API as described here. The tool acts as a simple graphical interface for query design.
Example: Using Visual Tools