QGIS – Replacing Strings in Multiple Array Elements by Commas

arrayattribute-tableexpressionqgisreplace

In QGIS 3.18, I have a vector layer of 1000+ features with 100+ attributes, many of which are strings of concatenated elements (substrings) delimited by commas. Some of those elements need to be replaced by other elements – entire elements as delimited, not parts of elements.

What I have done in the past to get the job done is to use replace() expressions with 20+ clauses in them. Besides being long and unwieldy, this method was extremely impractical due to the fact that some of the elements are contained as substrings within other elements: for example, one element might be '51.1', another might be '51.11' or '51.111', but these need to be treated and replaced differently. With the replace method, this got very messy very fast and required a lot of cleanup work.

I am looking to do this with an array function, which recognizes delimited elements. I have tried several attempts, such as: array_to_string(regexp_replace(string_to_array("Field_A",','),'51.1','String1')) which the Field Calculator recognizes as a valid expression but which returns no results, and

array_to_string(
    array_foreach(
        string_to_array("Field_A",','),
        regexp_replace(@element,'51.1','String1')
    )
)

which returns results but seems not to be identifying the delimited elements, as I have the same problem as with replace. Example: 51.11 is replaced as String11.

I need to be able to list many substring replacements in one expression, if possible.

As an example of the sort of thing I want to do, see the last line of the table below:

Note: I cannot use array_replace as I am unable to upgrade to QGIS 3.20 on this device at the moment, and this function is unavailable on 3.18.

Best Answer

You can achieve this combining arrays with maps (key:value pairs, see help Maps Functions) and the following expression, where in line 4 you list the elements you want to replace, using the pattern 'value_old','value_new'.

Explanation: The expression creates an array of all inputs in fieldA (line 6) and for each element (line 5) looks in the map (the one from line 4, created as a variable @map: line 2/3) at what position a key with this values from the array occurs in the map and returns the corresponding value (function map_get, line 7):

array_to_string (
    with_variable(
        'map',
        map('51.1','String1','51.11','String2','51.111','String3','51.13','String4','51.15','String5'),
        array_foreach (
            string_to_array (fieldA),
            map_get (@map,@element)
        )
    )
)

Related Solutions

QGIS – Expression to Aggregate Values from an Array

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

QGIS – How to Remove Multiple Elements from an Array Using QGIS Expressions

You can use array_filter() combined with in operator, like:

array_filter(array(1,2,3,4,5,6), @element not in (4,5,6))

Best Answer

Related Solutions

QGIS – Expression to Aggregate Values from an Array

QGIS – How to Remove Multiple Elements from an Array Using QGIS Expressions

Related Question