Filtering array with attribute from another layer in QGIS

Question

I have two layers with very similar tables

Layer 1:

id	section
1.1	A
1.2	A
1.3	A
2.1	B
3.1	C
3.2	C

At first I needed to summarize all id ́s that belong to one section (e.g.: '1.1, 1.2, 1.3' for section A)

Here is the code:

array_to_string(
 array_distinct(
 array_sort(
 array_agg("id",group_by:="section"))))

Now there is a second layer which unfortunately has a very similar table compared to the first layer. The second layer only contains one more field ("type"). However, in this example it is not possible to join these layers or add the information to the first table which would make things much easier I guess.

Layer 2:

id	section	type
1.1	A	true
1.2	A	true
1.3	A	false
2.1	B	true
3.1	C	false
3.2	C	true

My goal is to filter the array (e.g.: '1.1, 1.2, 1.3') depending on the value in the field "type" from the second layer. I only want to display the id ́s of one section for which "type" is true (e.g. '1.1, 1.2' for section A; '2.1' for B; '3.2' for C) Please keep in mind that these are two different layers.

The expression array_agg() also offers a filter, but I wasn ́t able to perform this referring to another layer. I tried attribute(get_feature()) as a filter expression but it didn ́t work. Also array_filter() didn ́t solve my problem.

• Kasluk24: In your example, the values in the ‘id’ and ‘section’ columns are identical in both layers. I assume this is not the case in reality, otherwise you could perform the aggregation in layer 2. Is that correct?
• Andrea Giudiceandrea: Why it is it not possible to join the layers?
• delmarzo: Yes the columns are identical. Unfortunately, this is the case. As I said, in this example I cannot edit the first layer due to restrictions. @AndreaGiudiceandrea, the expression worked so far but order_by doesn´t work. I think it needs to be group_by but this expression is not possible with aggregate.
• Andrea Giudiceandrea: @delmarzo, it seems to me the result of the expression provided by me in my answer is actually what you asked in the question. In fact, Section A = 1.1,1.2, Section B = 2.1, Section C = 3.2. If you think it isn't, please better clarify what is the desired output for each row in layer 1.

Answer 1

You need to use the aggregate function. Provided the fields "id", "section" and "type" are string fields and 'layer_2' is the name of the second layer:

array_to_string(
 array_distinct(
 aggregate('layer_2', 'array_agg', "id", attribute(@parent,'section')="section" AND "type"='true', order_by:="id")
 ),
 ', '
)

Using such expression to create a string virtual field named "aggregated" in layer_1, the resulting table will be:

id	section	aggregated
1.1	A	1.1, 1.2
1.2	A	1.1, 1.2
1.3	A	1.1, 1.2
2.1	B	2.1
3.1	C	3.2
3.2	C	3.2