Expression to filter array of features based on relationship to another layer

Question

I am tracking renewable projects in my country. I have a "Developments" layer, which shows polygons on a map representing projects. I also have a "Stakeholders" table (no geometry) that lists project developer companies. Therefore, I can track that "Project A" was developed by "Company X" and "Company Y".

The layers are related in a many-to-many relationship as follows:

where the names in red represent the names of the relations in QGIS' project properties.

I have over 700 developments in my project. I have linked developers to most of them, but some don't have a developer yet as I am still investigating.

I need to find the right advanced filter (expression) that will help me filter my attribute table to show only the developments that don't have a developer yet.

I have tried with the relation_aggregate() function as follows:

relation_aggregate(
   relation:= 'Developers',
   aggregate:= 'array_agg',
   expression:= "stakeholder_id"
)

and manage to get an array with the "fid" of the developers of each development, i.e. [12, 17, 104].

However, I am stuck at the next step: how do I filter out only those cases with a returned array that's empty? i.e. those that are []

I have tried:

relation_aggregate(
   relation:= 'Developers',
   aggregate:= 'array_agg',
   expression:= "stakeholder_id"
) IS NULL

but it does not work.

Answer 1

Instead of IS NULL (because an empty array - i.e. [] - is not NULL), you probably want to filter by length of array.

e.g.

array_length(
   relation_aggregate(
       relation:= 'Developers',
       aggregate:= 'array_agg',
       expression:= "stakeholder_id"
   )
) > 0