How to query Aggregate using the filter on related types [Cognite Official]

In Fusion Data Modeling(FDM) when you try to use aggregate queries currently only supports filtering on a View's own properties. Currently, users cannot filter on related types in aggregate queries. See the below example: In Fusion Data Modeling(FDM) when you try to use aggregate queries currently only supports filtering on a View's own properties. Currently, users cannot filter on related types in aggregate queries. See the below example:

If you refer to the below images you can see that the Actor is related to the Movie. You can see that the relation data is being populated in the Data Management as well.

However, when you try to filter out any of the related types in aggregate queries you will get an error message that the Field is not defined.

The reason is that the aggregate endpoint is serviced by a different backend(elastic search) compared to querying data in FDM and this service cannot/does not normalize any relationships and only supports aggregations of data in a single container/view.

Resolution to overcome the issue

To overcome this issue, you can now throw in a call to the DMS REST API’s instance aggregation operation. You can use an equals filter on the direct relation property, specifying the space and externalId of the target node as the value in the equals filter as shown below. 

API Endpoint:

https://cluster_name.cognitedata.com/api/v1/projects/project_name/models/instances/aggregate

Request:

{ 

   "aggregates": [ 
       { 
           "count": { 
               "property": "externalId" 
           } 
       } 
   ], 
   "view": { 
       "type": "view", 
       "space": "<space of related node>", 
       "externalId": "some_view", 
       "version": "version" 
   }, 
   "limit": 100, 
       "filter": { 
           "equals": { 
               "property": [ 
               "<space of related node>", 
               "some_view/version", 
               "DirectRelationProperty" 
               ], 

               "value": { 
                   "space": "<space of related node>", 
                   "externalId": "<Replace your external ID>" 
               } 
           } 
       }, 
   "instanceType": "node" 
}