QGIS – Expression to Aggregate Values from an Array

Question

In a calculated field in QGIS 3.10.2 I wan't to aggregate values from an array field. For this aggregation I'm using a filter to compare matching id's and return value from another field. I can aggregate values from a text or a numeric field, but I don't know how to reproduce this method with a list of values.

To be more precise:

• myTargetLayer: a reference table which contains id and text values

	idTargetLayer (integer)	myField
1	1	Grenache N
2	2	Cinsaut N
3	3	Carignan N
4	4	Viognier B
5	5	Chenin B

• mySourceLayer: a layer which contains an array field of id's of target layer and a text field. In this text field I wan't to aggregate text values of the target layer (called 'myField' here). For example if array field is '1,2,3' I wan't to have 'Grenache N, Cinsault N, Carignan N'.

	id	idTargetLayer (array)	Aggregate Field
1	9	4	Viognier B
2	8	1
3	7	1	Grenache N
4	6	2, 1
5	5	2, 1
6	4	1	Grenache N
7	5	1, 5
8	2	2, 1	Cinsaut N
9	19	2, 1	Cinsaut N,Cinsaut N
10	18	2, 1	,
11	17	2, 1	2,1
12	16	2, 1

Simple aggregation

aggregate(
layer:=layer_property('myTargetLayer','name'),
aggregate:='concatenate', 
expression:="myField",
filter:= attribute(@parent,'id_source_layer') = attribute($currentfeature,'id_target_layer')
)

If I try to iterate through each value of the array, it returns the same duplicated value.

array_to_string(
    array_foreach(attribute($currentfeature,'id_source_layer'),
        aggregate(
        layer:=layer_property('myTargetLayer','name'),
        aggregate:='concatenate', 
        expression:="myField",
        filter:= @element = attribute($currentfeature,'id_target_layer')
        )
    )
)

How to write an aggregate expression which iterates through each value of an array?

Answer 1

Note that if "idTargetLayer" in mySourceLayer already is an array, you need to remove string_to_array() for this field, see comment by OP below.

Option 1 - Independent "ID", but more complex expression

If your IDs are numerical but have nothing to do with the featureIDs of the layer, you can use this, a little more complex, expression:

array_to_string(
    array_foreach(
        array_foreach(
            array_foreach(
                string_to_array("idTargetLayer",', '),to_int(@element)), -- create an integer array of the values to search for
                    array_find(
                        array_foreach(
                            aggregate('myTargetLayer','array_agg',"idTargetLayer" || '|' ||"myField"), -- create an array of the IDs and myField values, intended to work similar to a Python-Dictionary, | is the unique separator between key and value
                        regexp_substr(@element,'[0-9]*')), -- extract the IDs of this 'Dictionary'
                    @element -- get the index of the value we will extract later
                    )
        ),
        array_get(
            array_foreach(
                aggregate('myTargetLayer','array_agg',"idTargetLayer" || '|' ||"myField"), -- create an array of the IDs and myField values, intended to work similar to a Python-Dictionary, | is the unique separator between key and value
            regexp_substr(@element,'[0-9][|](.*)')),  -- extract the Values of this 'Dictionary'
        @element) -- get the current integer array element of the value dict
    ),
',')

Its a little hard to explain, and even confusing myself, but I hope you get the idea of it. However, as the result shows, its working fine:

Option 2 - "ID" is related to layers featureID and easier expression

The following expression should work if the id's of myTargetLayer are the layer's featureID's:

array_to_string(
    array_foreach(
        array_foreach(
            string_to_array("idTargetLayer"), -- idTargetLayer is a string so we need to convert it to an array first
            to_int(@element)), -- idTargetLayer is a string field, so we need to convert the numbers to integer before we can look it up
    attributes(get_feature_by_id('myTargetLayer',@element))['myField'] -- get the attribute of ['myfield'] of the layer 'myTargetLayer' for the featureID '@element', where @element is the extracted integer of 'idTargetLayer' array
    )
,',') -- convert the gathered array back to a string