[Qgis-user] Select features based on an array with elements from another layer

[chris hermansen]

Ann-Christin and list

On Wed, Jul 8, 2026, 03:56 Ann-Christin Voss via QGIS-User <
qgis-user at lists.osgeo.org> wrote:

> Dear list,
>
> I try to select some features vie their id(integer) based on an array
> containing ids (also integers) I get from another layer.
>
> In SQL I would write SELECT * FROM base WHERE id in (SELECT DISTINCT id
> FROM filter_layer WHERE ...filter...)
>
> Naturally I would write the expression in 'Select features by expression'
> like this but it dosen't work (no features selected):
> array_contains(
> array_distinct(
> aggregate(
> 'filter_layer',
> 'array_agg',
> "ID",
> ...filter...
> )
> )
> "id")
>

> I would assume it iterates through the ids in the base and if it's found
> in the array it's true and therefore selected and if it's not in there,
> it's false and therefore not selected.
>
> Instead I have to  write this (but it also would't work if didn't have
> unique id's in the base layer because it then would only select the first
> found):
> array_find(
> array_distinct(
> aggregate(
> 'filter_layer',
> 'array_agg',
> "id",
> ...filter...
> )
> ),
> "id"
> ) <> -1
>
> (
> https://gis.stackexchange.com/questions/344412/select-features-matching-the-values-in-an-array-qgis-3-10-expression
> )
>
>
> I simplified some things for testing and try to select in the base_layer
> without elements from another layer:
> "id" IN (10,11,12,13) works (10 and 11 true --> selected, 12 and 13 false
> --> not selected)
> "id" IN array(10,11,12,13) doesn't work (no Objects selected)
>
> I find this very hard to understand and I don't know how to explain this
> to costumers who before used programs where they select based on sql
> expressions.
> Also I can't figure out how to select the features if the id(in this case)
> isn't unique (see question above).
> I chose the "select by expression" tool because I need the selection in
> the base_layer and not a separate layer containing the filtered features.
> Could someone please explain it to me or tell me a more "natural feeling"
> way to select the features based on an array?
> (I'm using QGIS 3.44)
>

Is there a reason you cannot use a.second table containing a list of all
the desired IDs and joining the two tables together?

As in

Select * from base join wanted on base.id = wanted.id

Joining two tables this way is basic idiomatic SQL.

Of course it means you have to put your list of wanted IDs in the "wanted"
table rather than in some other structure. This leads to the question "how
do you define the list of wanted IDs?" - maybe there is some reason for
wanting those IDs that can be expressed as a WHERE clause... (an attribute
value or a spatial expression or a combination thereof).

I find it kind of odd that people are walking around with lists of
interesting IDs. Usually that would mean they had found some property
shared by the objects with those IDs

>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20260708/f10d3803/attachment-0001.htm>