iinsight | Developer portal
www.iinsight.biz

Query Builder

The Query Builder feature allows API consumers to perform more specific and versatile queries. By using various operators, users can filter records based on specific conditions, making data retrieval more efficient and tailored to their needs. This functionality is particularly useful when dealing with large datasets where precise data extraction is necessary. APIs that include the Query Builder feature will be indicated with (Query Builder) and will have a link to this section for more details.

Parameters

These are the parameters that are part of the API request. None of these parameters are mandatory, and they allow you to customize the query to retrieve specific data based on your requirements.

Parameter Description Example Default Value
fields Fields to select, separated by commas with no spaces. field1,field2,... all
query JSON string specifying filter conditions. {"field1": ["value1", "value2"], "field2": [">=10"]} {}
sort JSON string specifying sort conditions. {"field1": "asc", "field2": "desc"} {}
limit Number of records to return per page. 100 100
page Page number for pagination. 1 1

Response

The following are the response fields returned by the API:

Field Description
status Boolean indicating the success or failure of the request.
data Array of the retrieved records.
displayed_records Number of records displayed in the current response.
total_records Total number of records available matching the query.

Operators for Query

These operators are used to construct the query parameter, allowing for detailed filtering of records based on specific conditions. Operators can be placed in front of any value to create the desired condition. They enable you to create complex queries to retrieve exactly the data you need.

Operator Description Example Default Value
= Equal to id=["=30"] N/A
!= or <> Not equal to id=["!=30"] or id=["<>30"] N/A
< Less than id=["<30"] N/A
> Greater than id=[">30"] N/A
<= Less than or equal to id=["<=30"] N/A
>= Greater than or equal to id=[">=30"] N/A
%...% Contains description=["%John%"] N/A
%... Starts with description=["John%"] N/A
...% Ends with description=["%John"] N/A

Examples

For these examples, we will work with the billings API (api1/billings). We will use a limit of 1 to 3 records depending on the example and select fields relevant to the example. You can adjust the limit and fields as needed.

Example 1: Simple Request Without Query

This example demonstrates a simple request to retrieve records.

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1

Response:
{
    "status": true,
    "data": [
        {
            "id": "1",
            "description": "OES Vocational Re-Education Assessment / Vocational Re-Deployment Assessment",
            "case_id": "3",
            "case_info": {
                "case_number": "3",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 5099
}
Example 2: Simple Query

This example demonstrates how to use the Query Builder to filter records. To filter by any field, specify the field name as the key and the filter values as an array, even if there is only one value. This ensures consistent formatting and flexibility in your queries.

In this specific example, we filter records to retrieve an entry with the ID 8781.

Request:

/api1/billings?fields=id,description,case_id,time_based&query={"id":["8781"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "8781",
            "description": "Return to Work Same Employer",
            "case_id": "3",
            "case_info": {
                "case_number": "3",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 1
}
Example 3: Query with Equal Operator

This example demonstrates how to use the equal operator in the Query Builder to filter records. By specifying the equal operator, you can retrieve records that match a specific value exactly.

In this specific example, we filter records to retrieve an entry with the ID 8781.

Request:

/api1/billings?fields=id,description,case_id,time_based&query={"id":["=8781"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "8781",
            "description": "Return to Work Same Employer",
            "case_id": "3",
            "case_info": {
                "case_number": "3",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 1
}
Example 4: Query with Not Equal Operator

This example demonstrates how to use the not equal operator in the Query Builder to filter records. By specifying the not equal operator, you can retrieve records that do not match a specific value.

In this specific example, we filter records to retrieve entries with IDs not equal to 8781, and limit the result to 1 record.

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1&query={"id":["!=8781"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "1",
            "description": "OES Vocational Re-Education Assessment / Vocational Re-Deployment Assessment",
            "case_id": "3",
            "case_info": {
                "case_number": "3",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 4883
}
Example 5: Query with Less Than Operator

This example demonstrates the use of comparison operators in the Query Builder to filter records. By specifying operators such as less than, you can retrieve records that meet the desired criteria.

In this specific example, we filter records to retrieve entries with IDs less than 5000, and limit the result to 2 records.

Request:

/api1/billings?fields=id,description,case_id,time_based&query={"id":["<5000"]}&limit=2

Response:
{
    "status": true,
    "data": [
        {
            "id": "1",
            "description": "OES Vocational Re-Education Assessment / Vocational Re-Deployment Assessment",
            "case_id": "3",
            "case_info": {
                "case_number": "3",
                "case_n": "1"
            },
            "time_based": "1"
        },
        {
            "id": "2",
            "description": "Functional Assessment",
            "case_id": "2",
            "case_info": {
                "case_number": "2",
                "case_n": "2"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 2,
    "total_records": 1834
}
Example 6: Query with Greater Than Operator

This example demonstrates the use of comparison operators in the Query Builder to filter records. By specifying operators such as greater than, you can retrieve records that meet the desired criteria.

In this specific example, we filter records to retrieve entries with IDs greater than 5000, and limit the result to 2 records.

Request:

/api1/billings?fields=id,description,case_id,time_based&query={"id":[">5000"]}&limit=2

Response:
{
    "status": true,
    "data": [
        {
            "id": "5001",
            "description": "Activity",
            "case_id": "784",
            "case_info": {
                "case_number": "784",
                "case_n": "1"
            },
            "time_based": "1"
        },
        {
            "id": "5002",
            "description": "X1 Activitie",
            "case_id": "791",
            "case_info": {
                "case_number": "791",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 2,
    "total_records": 3049
}
Example 7: Query with Less Than or Equal To Operator

This example demonstrates how to use the less than or equal to operator in the Query Builder to filter records. By specifying this operator, you can retrieve records that are less than or equal to a specific value.

In this specific example, we filter records to retrieve entries with IDs less than or equal to 5000, and limit the result to 1 record.

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1&query={"id":["<=5000"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "1",
            "description": "OES Vocational Re-Education Assessment / Vocational Re-Deployment Assessment",
            "case_id": "3",
            "case_info": {
                "case_number": "3",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 5000
}
Example 8: Query with Greater Than or Equal To Operator

This example demonstrates how to use the greater than or equal to operator in the Query Builder to filter records. By specifying this operator, you can retrieve records that are greater than or equal to a specific value.

In this specific example, we filter records to retrieve entries with IDs greater than or equal to 5000, and limit the result to 1 record.

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1&query={"id":[">=5000"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "5000",
            "description": "travel expense",
            "case_id": "697",
            "case_info": {
                "case_number": "697",
                "case_n": "3"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 5000
}
Example 9: Query with Specific Text

This example demonstrates how to filter records to retrieve entries that match a specific text. By specifying the exact text, you can retrieve records that match the specified value.

In this specific example, we filter records to retrieve an entry with the description "Functional Assessment".

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1&query={"description":["Functional Assessment"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "2",
            "description": "Functional Assessment",
            "case_id": "2",
            "case_info": {
                "case_number": "2",
                "case_n": "2"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 4
}
Example 10: Query with Contains

This example demonstrates how to use the contains operator in the Query Builder to filter records. By specifying this operator, you can retrieve records that contain a specific substring.

In this specific example, we filter records to retrieve entries that contain the text "Assessment" in the description.

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1&query={"description":["%Assessment%"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "2",
            "description": "Functional Assessment",
            "case_id": "2",
            "case_info": {
                "case_number": "2",
                "case_n": "2"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 4
}
Example 11: Query with Starts With

This example demonstrates how to use the starts with operator in the Query Builder to filter records. By specifying this operator, you can retrieve records that start with a specific substring.

In this specific example, we filter records to retrieve entries that start with the text "Functional" in the description.

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1&query={"description":["Functional%"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "2",
            "description": "Functional Assessment",
            "case_id": "2",
            "case_info": {
                "case_number": "2",
                "case_n": "2"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 4
}
Example 12: Query with Ends With

This example demonstrates how to use the ends with operator in the Query Builder to filter records. By specifying this operator, you can retrieve records that end with a specific substring.

In this specific example, we filter records to retrieve entries that end with the text "Assessment" in the description.

Request:

/api1/billings?fields=id,description,case_id,time_based&limit=1&query={"description":["%Assessment"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "2",
            "description": "Functional Assessment",
            "case_id": "2",
            "case_info": {
                "case_number": "2",
                "case_n": "2"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 1,
    "total_records": 4
}
Example 13: Query with Multiple Values

This example demonstrates the flexibility of the Query Builder to filter records by specifying multiple values for a field. By using an array of values, you can retrieve records that match any of the specified criteria.

In this specific example, we filter records to retrieve entries with the IDs 5000, 7024, and 8781.

Request:

/api1/billings?fields=id,description,case_id,time_based&query={"id":["5000","7024","8781"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "5000",
            "description": "travel expense",
            "case_id": "697",
            "case_info": {
                "case_number": "697",
                "case_n": "3"
            },
            "time_based": "1"
        },
        {
            "id": "7024",
            "description": "Functional Ax",
            "case_id": "989",
            "case_info": {
                "case_number": "989",
                "case_n": "231"
            },
            "time_based": "1"
        },
        {
            "id": "8781",
            "description": "Return to Work Same Employer",
            "case_id": "3",
            "case_info": {
                "case_number": "3",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 3,
    "total_records": 3
}
Example 14: Query with Intervals

This example demonstrates how to use interval operators in the Query Builder to filter records. By specifying range operators such as greater than or equal to, and less than or equal to, you can retrieve records that fall within a specific range.

In this specific example, we filter records to retrieve entries with IDs between 5000 and 6000, inclusive, and limit the result to 2 records.

Request:

/api1/records?fields=id,description,case_id,time_based&limit=2&query={"id":[">=5000","<=6000"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "5000",
            "description": "travel expense",
            "case_id": "697",
            "case_info": {
                "case_number": "697",
                "case_n": "3"
            },
            "time_based": "1"
        },
        {
            "id": "5001",
            "description": "Activity",
            "case_id": "784",
            "case_info": {
                "case_number": "784",
                "case_n": "1"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 2,
    "total_records": 792
}
Example 15: Query with Multiple Conditions

This example demonstrates how to use multiple conditions in the Query Builder to filter records. By specifying multiple field-value pairs, you can retrieve records that match all the specified criteria.

In this specific example, we filter records to retrieve entries that contain the text "Assessment" in the description and have a case_id of 2.

Request:

/api1/billings?fields=id,description,case_id,time_based&query={"description":["%Assessment%"],"case_id":["2"]}

Response:
{
    "status": true,
    "data": [
        {
            "id": "2",
            "description": "Functional Assessment",
            "case_id": "2",
            "case_info": {
                "case_number": "2",
                "case_n": "2"
            },
            "time_based": "1"
        },
        {
            "id": "8",
            "description": "Workplace Assessment Report",
            "case_id": "2",
            "case_info": {
                "case_number": "2",
                "case_n": "2"
            },
            "time_based": "1"
        }
    ],
    "displayed_records": 2,
    "total_records": 2
}