postgispostgresqlqgis
I have a PostGIS layer which contains text arrays. At the moment it is not possible to use field values resolved by the expression builder. If the array contains only one item, the field value will be ok, having two or more values within the array, the field value preview returns an empty string. Do I miss something here or could this be a bug?
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
"Field" not in ('Value 1', 'Value 2')
This should work for you.
Best Answer
Regarding the preview values, yes, it looks like a bug. For int arrays, it's either Null or empty string (even if the array contains only 1 value).
That being said, you can still apply the
array_*functions to work with this field (ex:array_contains("sa",'F'))