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

  • 14 November 2023
  • 2 replies
  • 150 views

Userlevel 1
Badge +1

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.
uF7hajKag2MfguIfxyk42FK6tp1wG6-6N_y9x9NhDkujNvHIYgvpZtGxioxNGKiWY8qdwGtjsAB0G2EIUe_FPViiD8B-wZD0sSzAgyrLk2g2XT1No0j1_RUCx_4Y3DzivBd8Ym1j8hpFthikhJjX_d0LIo0e8xF8K_x40v0WByS3OPmoeZKsvGdWOhm_3EZhd0GkIhCX3w0XJ5c55WUJmvEQGnwGP0L9Xm-1mZKSaceNuMBxZykxxl11w2DJLHoroAvtGdVI1_yi48J0Wk-hwXniiyDQ6RuN8Yt-pLpA8I8H0A

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.
Sta74OMibudYuv2u-fubvUd6C3-d5OyG_Gpd6gpZrPH6SUJDN_hg-nRdd37k5oYCcuv03Usou4tBxMFLNYCW0CKEEsm3iaTsCP73A5Qnfv2lAJnSJHVdJKkMKO6wcxGA18_yiJ3qTKGKOH17R9-cL8U

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"
}

 


2 replies

Badge +5

I tried this approach but I’m getting an error:

{
    "error": {
        "code": 400,
        "message": "Unknown property: <name_of_space>.<name_of_view>/<view_version_number>.DirectRelationProperty"
    }
}

Any ideas on what could be the problem?

Userlevel 6
Badge

Hi @Shashan Udawatte would you be able to have a look at this please? 

 

Reply