QGIS – Advanced Labeling in QGIS to Automatically Extract Data from New Fields and Create Arrays

Question

I'd like to be able to easily get quick data from different projects I work in in order to avoid having to do sql queries all the time. So here's the thing, today I managed to get the following expression working fine using some advanced labeling settings in QGIS. I used this as a guide: QGIS sum selected line lengths

concat('Longitud total: ',round(array_sum(aggregate(
layer:= 'streetaxis longtotal_fix',
aggregate:='array_agg',
expression:=$length,
filter:=is_selected())),2),' m')

It simply returns the sum of the lengths of the selected entities of a defined line layer. Then I configured the label's position to fix it in the top-left corner. I am aware it's kinda a sloppy way to do that, but it does the job and it is certainly a quick and efficient solution for me.

Now I'd like to do something more advanced following the same method for a different layer (a points layer). First, I'd like to get the sum of several fields individually. I highligthed the fields I am referring to on the table screenshot below. These fields name always begin with "pre_" and they are automatically created and updated on a quarterly basis in the database. So, I can't just put the name of each field in the script.

So far I got this simplified code working for just one field:

array_sum(aggregate(
layer:= 'aigues_consums',
aggregate:='array_agg',
expression:=coalesce(per_201903,0),
filter:=is_selected()))

Note: I had to use coalesce because of NULL values, which prevented the expression to work when a point with null data was selected.

What it does right now is return the sum of the "per_201903" field for the selected points. So, I'd like to get the data for each field and then to display them independently. Since all the fields I want to get the data from begin with "per_, I had in mind using some function to easily detect the fields which their names contain "per_", but I don't know how to do this – or even if it is possible. I suspect it can be done combining it with array_foreach function, and creating a concatenated variable to increase year (+1) and month (+3),but this definitely surpasses my current knowledge of QGIS.

For example, I am looking for an output like this:

per_201903: 452
per_201906: 652
per_201909: 422
per_201912: 395
per_202003: 488
per_202006: 680
[etc]

Answer 1

1. Use this expression to get an array with all fieldnames starting with per_:

   array_filter( 
       map_akeys(attributes()),
       left (@element,4)='per_'
   )
   

   • attributes() gets a map of all fieldnames:values - see docu
   • map_akeys returns an array of all fieldnames - see docu
   • array_filter() to keep only fieldnames starting with 'per_' - see docu

2. Now for each of these fieldnames (elements of this array), run your expression separately, using array_foreach() .

3. To run your expression for each of these fieldnames, create the expression as a string and access the varying fieldnames with @element (this inserts the values of the elements from the array of step 2) by concatenating @element and the rest of the expression with pipes || to get everything in one string . Then convert this string to a working expression with eval():

array_foreach(
    array_filter( 
        map_akeys(attributes()),
        left (@element,4)='per_'
    ),
    eval (
        'array_sum(
            aggregate(
                layer:= ''your_layer_name'',
                aggregate:=''array_agg'',
                expression:='  || @element  || ',
                filter:=is_selected()
            )
        )'
    )
)

Remark: inside the eval() function, you need to define the expression as a string, defined by single quotes '. As the expression that should be evaluated contains itself a string ('your_layer_name'), you must mask the single quotes here so that QGIS understands that the string used to be evaluated by eval is not over. Use two single quotes '' (not double quotes ") for masking.

Visualizing the sum for all selected points (yellow) of the values of all fields starting with per_; selected points are additionally labeled with their individual values: