6

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

target

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'.

source

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?

3
  • 3
    Can you add an example what myField contains? – MrXsquared Dec 23 '20 at 17:05
  • 3
    Would be helpful to see the structure of your attribute table and see there what exactly you want to do - the description is a bit abstract – Babel Dec 23 '20 at 17:06
  • 1
    I edited my post to be more explicit. – GeoGyro Dec 23 '20 at 17:29
5

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:

enter image description here

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

enter image description here

7
  • In both methods, expression dialog return an error message Impossible to convert '[idTargetLayer]' to integer, although it's an int field type. MyTargetLayer is a PostgresSQL table with a sequence who autoincrement id, could it be the source of the problem ? – GeoGyro Dec 28 '20 at 10:38
  • @GeoGyro you can try replacing all "idTargetLayer" by to_int("idTargetLayer") – MrXsquared Dec 28 '20 at 10:52
  • Same issue with to_int("idTargetLayer"). – GeoGyro Dec 28 '20 at 10:58
  • 1
    @GeoGyro For testing I added both tables from your example as CSV. (I guess thats also the reason why my feature id was +1...) – MrXsquared Dec 28 '20 at 12:48
  • 1
    I managed to replicate your solution with the same results using CSV. In my case it's a little different because I use PostgreSQL/PostGIS tables and an array datatype column, so I need to adapt your method 2 by not specifying string_to_array (it's already an array). – GeoGyro Dec 28 '20 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.