Query - Get Data from an App

Query Endpoint

The /Query endpoint is used to query entity data from any app within Kahua. This is one of the most commonly used endpoints due to the flexibility it provides in querying specific data from any app in Kahua

The URL for the /Query endpoint has the following structure:

POST /v2/domains/{{domain}}/projects/:projectId/query?returnDefaultAttributes=true

  • {{domain}}: The domain the query should run against, Replace any spaces in the domain name with an underscore.

  • :projectId: The project ID of the project the query should run against.

  • returnDefaultAttributes: This is an optional URL parameter that defaults to false. When set to true, this will return all attributes for the queried entity regardless of whether they are null or not. This includes many system attributes that are part of every entity in Kahua. It is recommended that this parameter only be used when explicitly needed for things such as generating sample/mapping data sets, as it can greatly increase the size of the response payload when used on queries that return many records.

    Note that this endpoint uses a POST command rather than a GET, even though it is getting/pulling data from the server. This is because this endpoint requires a JSON query to be passed in via the Request Body of the API request (which GET requests do not support).

The v2 endpoint works exactly the same as the v1 endpoint, with the exception that the result set returned is in a more "standard" JSON format that is more compact, easier to read, and easier to parse:

Copy 
{
    "count": 1,
    "entities": [
        {
            "id": 5057677,
            "entityDef": "kahua_PeopleManager.kahua_Contact",
            "hubPath": "",
            "outwardReferences": [],
            "IsExchangeEntity": false,
            "IsEmployee": false,
            "MarkupEnabled": false,
            ...
        }
    ],
    "skipped": 0,
    "taken": 100
}

Everything else about the v2 endpoint works exactly the same as the v1 endpoint, including the structure of the request body.

The body of the request is where all the main details for the query are provided. This can range from very simple to very complex as there are numerous options that can be used to filter, change scope, and sort the data.

Basic request

At it's most basic (without optional conditions, etc.), this will return all instances of the entity data for the given project that the user has visibility into. The request body below will retrieve all properties from the Properties app (kahua_Properties) for the domain and project specified in the endpoint:

Copy 
{
  "PropertyName": "Query",
  "EntityDef": "kahua_Properties.Property"
}

  • PropertyName: Should be "Query"

  • EntityDef: The name of the entity being queried. This is prefixed with the app name. App and entity names can be found by examining the app definition, or using the Live Entity viewer in kBuilder.

    App and entity names needed for this request can be found in our data dictionary. Refer to this page for more details and a link to download the data dictionary.

If you know the ID or IDs of the entity you are querying for, it's preferable to use the "EntityRange" attribute rather than adding a data condition. The entity range can be a single ID or a comma-separated list of multiple IDs:

Copy 
 {
    "PropertyName": "Query",
    "EntityDef": "kahua_Project.Project",
    "Name": "Projects",
    "EntityRange": "6209560,6209573"
}

A filter/condition (similar to a "WHERE" clause in SQL) can be added to filter the result set to specific record or records. The sample below will find any records with an "Id" attribute equal to 6209560. Since ID is a unique attribute, a max of one record will be returned

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "Condition": [
        {
            "PropertyName": "Data",
            "Path": "Id",
            "Type": "EqualTo",
            "Value": "6209560"
        }
    ]
}

  • PropertyName: One of the following specified values that define what piece of data the condition is comparing against for the filter. "Data" is the most common as it compares the given data element (attribute) against the given Value:

    • Data: Filters against the data attribute provided in the "Path"

    • Entity

    • Flag

    • HubPath: Filters against the current HubPath (workflow step) that a record is in

    • Items

    • Link

    • Match

    • NoneOf

    • Parameter

    • PathedPartition

    • Permission

    • Setting

    • Transport

    • Variable

    • Visual

    • WorkBreakdown

  • Path: If "PropertyName" is "Data", then the "Path" should specify the path to the attribute being used as part of the condition

  • Type: One of the following specified values, whose operation is applied against the given Value.

    • Contains

    • DoesNotContain

    • EndsWith

    • EqualTo

    • GreaterThan

    • GreaterThanOrEqualTo

    • HasAttribute

    • HasLink

    • HasNoAttribute

    • HasNoLink

    • HasNoValue

    • HasValue

    • In: Use comma-separated list in Values property

    • Is

    • IsDistinct

    • IsFalse

    • IsTrue

    • LessThan

    • LessThanOrEqualTo

    • NotEqualTo

    • NotIn

    • StartsWith

    • WhereAll

    • WhereAny

  • Value: The value being used in the condition

Multiple conditions can be applied to a query by using the AllOf or AnyOf Property name. Using AllOf will perform a logical AND on the child conditions (only records that meet all child conditions will pass the filter). Using AnyOf will perform a logical OR on the child conditions (records that meet at least one of the child conditions will pass the filter). The condition below checks for records with a ModifiedDateTime in a specified time frame:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "Condition": [
        {
            "PropertyName": "AllOf",
            "_children": [
                {
                    "PropertyName": "Data",
                    "Path": "ModifiedDateTime",
                    "Type": "GreaterThanOrEqualTo",
                    "Value": "2020-11-29T00:00:00.000"
                },
                {
                    "PropertyName": "Data",
                    "Path": "ModifiedDateTime",
                    "Type": "LessThan",
                    "Value": "2020-11-30T00:00:00.000"
                }
            ]
        }
    ]
}

This filter will only return entities that have been created or updated after a specific marker or timestamp.

To filter entities by their markers, use the MarkerFilter object, which has a single field From, which is an object with either:

  • MarkerId: The ID of a marker to query after

  • DateTime: An ISO 8601 timestamp to query after

If both MarkerId and DateTime are specified, then only entities that have both markers with an ID greater than MarkerId and a timestamp greater than DateTime

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_AEC_DailyReport.DailyReport",
    "MarkerFilter": {
        "From": {
            "MarkerId": "{{kpc-seed-marker}}"
            // Pick one ↕
            // "DateTime": "{{kpc-seed-isoTime}}"
        }
    }
}

Scalar explicits can be used to query for specific attributes of entities (when they are scalar values , that is, not references to other entities). This can help to reduce clutter when querying data. For example, scalar explicits could be used to query for only the Number and Name of a certain record reducing the response payload size significantly.

Combining "ImplicitsDisabled" (see below) with ScalarExplicits can reduce the response size even more.

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Project.Project",
    "ImplicitsDisabled": "True",
    "ScalarExplicits" : [
        {
            "PropertyName": "Scalar",
            "Path": "Number"            
        },
        {
            "PropertyName": "Scalar",
            "Path": "ShortLabel"            
        }

    ]
}

Typically, calls to the query API are limited to records that belong to the project ID that is included in the URL. It is possible to change the scope of the query to change the scope of the records the query applies to. The example below queries for pay requests (the actual entityDef name is kahua_ContractInvoice.ContractInvoice) that are in any partition in the domain:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_ContractInvoice.ContractInvoice",
    "Partition": {
        "Scope": "Any"
    }
}

Valid values for the scope variable are:

  • Any: Defines the scope as all records in the domain regardless of partition.

  • Domain: Defines the scope as the root domain (DomainPartitionId=0).

  • Partition: Defines the scope as the partition. For instance, for RFIs this could be the pre-construction or post-construction list.

  • DomainPartition: Defines the scope as the current project. This is the default scope for queries.

It's important to understand the scope of the app that you are querying from. For Instance, the Properties app is a domain scoped app, so all records in the domain have a projectId (partition) of zero (0) even though those records are visible when using the Properties app from within a project (all projects have visibility into the data for the domain defined app). If you were to query the properties app for a project within the domain, no properties would be returned since the properties all belong to the root (0) project/partition.

To ensure queries don't return massive data sets that are inefficient and bog down the system, by default queries will return a maximum of 100 records. If there are more than 100 records in your result set, the returned result set will indicate the true record count, but only the first 100 records will be included in the result set:

Copy 
{
    "count": 216,
    "entities": [
        {
            "id": 6209560,
            "entityDef": "kahua_Properties.Property",
            "hubPath": "kahua_Properties.NoWorkflow\\Start",
            "attributes": [
                {
                // only 100 records are returned by default

To control the size of the result set, the query API supports "Take" and "Skip" parameters to allow retrieving data in pages. This will ensure predictable performance on individual API calls and ensure that API server resources are not overloaded:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "Take": "10",
    "Skip": "10"
}

In addition, the query API supports a TakeAll parameter to force the query to return all records. However, this parameter should not be used without explicit permission from Kahua as using the "TakeAll" parameter is not a best practice and only has specific instances where it may be needed.

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "TakeAll": "True"
}

The "TakeAll" parameter should not be used unless recommended by Kahua. If an application frequently runs query APIs that return extremely large data sets there is not only a high possibility that the query will time out and produce an error, it may also get disabled or disconnected to avoid any negative performance impacts it could cause on the platform

The preferred method to handle larger data sets is to use the "Take" and "Skip" parameters to specify pages of data. This will ensure predictable performance on individual API calls and ensure that API server resources are not overloaded:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "Take": "10",
    "Skip": "10"
}

Nearly all queries will return entities which themselves have child entities. For example, Property records contain a "PropertyManager" attribute which contains a reference to a kahua_Contact (from the Contacts app). That contact contains all the details of the PropertyManager. These nested structures can often be quite deep and cause the fully expanded representation of a record to become quite large (contacts reference companies, etc., etc.). To manage data and bandwidth effectively, by default child entities will only contain the entityDef (type) and ID of child entities in a record:

Copy 
{
    "count": 216,
    "entities": [
        {
            "id": 6112506,
            "entityDef": "kahua_Properties.Property",
            "hubPath": "kahua_Properties.NoWorkflow\\Start",
            "attributes": [
                {
                    "name": "MarkupEnabled",
                    "value": "False"
                },
                ...
                {
                    "name": "CreatedBy",
                    "entities": [
                        {
                            "id": 5211738,
                            "entityDef": "kahua_PeopleManager.kahua_Contact",
                            "hubPath": "",
                            "attributes": []
                        }
                    ]
                },
                {
                    "name": "PropertyManager",
                    "entities": [
                        {
                            "id": 5845459,
                            "entityDef": "kahua_PeopleManager.kahua_Contact",
                            "hubPath": "",
                            "attributes": []
                        }
                    ]
                },
                ...

If data from a child entity needs to be included in the query result, explicit parameters can be used to specify the child entities that need to be returned. For instance, if we need to know the name of the "PropertyManager" attribute, we can specify that the "PropertyManager" entity be included in the result:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "Explicits": [
        {
            "PropertyName": "Data",
            "Path": "PropertyManager"
        }
    ]
}

For multiple nested child entities, the explicit can use the dot notation to path all the way down to further child entities. For instance, if we need to know the company name for the Property Manager, we can include the company like this:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "Explicits": [
        {
            "PropertyName": "Data",
            "Path": "PropertyManager.Company"
        }
    ]
}

In cases like this when an explicit is multiple levels deep, all entities included in the path will be returned. In the above example, all attributes for the PropertyManager (kahua_PeopleManager.Contact) and the Company (kahua_CompanyManager.kahua_Company) will be included in the result.

When a query result set includes multiple records that reference the same child entity, and that child entity is included as an explicit, the entity details will only be included in the first record. All subsequent records that reference that exact same entity will only contain a reference. This is done to preserve bandwidth, as the data returned for that child entity will be the exact same for all records that reference the same child entity.

The sorting parameter can be used to sort the results of the query based on any attribute or attributes needed. Multiple sorts can be specified to handle secondary sorting (For example, sort first by DueDate then second by Name). Below is an example that will sort by State first, then City:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "Sorts": [
        {
            "PropertyName": "Data",
            "Path": "State",
            "Direction": "Ascending"
        },
        {
            "PropertyName": "Data",
            "Path": "City",
            "Direction": "Ascending"
        }
    ]
}

  • PropertyName: Should be "Data" to sort based on an attribute

  • Path: A path to the attribute to sort on

  • Direction: Either Ascending or Descending

The ImplicitsDisabled parameter tells the query engine to not include implicit attributes in the result set. This will cause the result set to not include anything for child entities, not even the entityDef and ID. This helps further reduce the size of the result set if child entities are not needed. The child entities for any explicits that are defined in the query will still be returned:

Copy 
{
    "PropertyName": "Query",
    "EntityDef": "kahua_Properties.Property",
    "ImplicitsDisabled": "True",
}

Without implicits disabled a result set would include the stub for child entity attributes such as the "CreatedBy" attribute below:

Copy 
{
    "count": 216,
    "entities": [
        {
            "id": 6209560,
            "entityDef": "kahua_Properties.Property",
            "hubPath": "kahua_Properties.NoWorkflow\\Start",
            "attributes": [
                {
                    "name": "MarkupEnabled",
                    "value": "False"
                },
                ...
                {
                    "name": "CreatedBy",
                    "entities": [
                        {
                            "id": 5211815,
                            "entityDef": "kahua_PeopleManager.kahua_Contact",
                            "hubPath": "",
                            "attributes": []
                        }
                    ]
                },

With the ImplicitsDisabled parameter set to true, child entities like "CreatedBy" would not be included in the result set at all.

Any attributes that have a null value will not be included in the result set of a query. If a known attribute does not exist in the result set, it can be assumed to be null. This further helps to reduce the size of the result set as some large entities often have numerous, empty attributes. The one exception is if attribute is an entity type and ImplicitsDisabled was specified in the query, in which case it would never be included in the result set.