Postgresql – Using postgres to select a filtered array

arraypostgresqlquery

I'm looking to select data from a table that contains an ID, and an array of values. But in the query results, i want to filter the array and remove values that overlap with a white list.

For illustrative purposes, the following query shows how the data would be formatted with a whitelist of values in a single column, and then a table i want to select from… that contains a unique ID, and the array of keywords.

with whitelist as (
    select distinct unnest(array['a','b','c','f','h']) as keywords
), raw_with_keyword_array as (
    select * from  (
        values 
            ('1234', array['a','c','e','h','q']),
            ('1235', array['a','d','e','f']) 
    ) as t (id, keyword_array)
)
select...

The expected results i'm looking for is two rows from the raw_with_keyword_array table, but filtered arrays not including the values from the whitelist.

I figure i probably need to select the data from the main table, and then do some kind of recursive sub query to filter out the whitelisted values, and put the results back into a new array?

Any suggestions would be appreciated!

E.g.

1234 {e, q}

1235 {d, e}

Best Answer

Not sure if this is the most efficient way to do it, but this works.

You do have to unnest all elements from keyword_array to be able to filter the unwanted out:

with whitelist (kw) as (
    values ('a'),('b'),('c'),('f'),('h')
), raw_with_keyword_array (id, keyword_array) as (
  values 
      ('1234', array['a','c','e','h','q']),
      ('1235', array['a','d','e','f']) 
)
select id, 
       array(select kw
             from unnest(ra.keyword_array) as t(kw)
             except 
             select kw
             from whitelist)
from raw_with_keyword_array ra;

Instead of except you could also use a where not exists:

....
select id, 
       array(select kw
             from unnest(ra.keyword_array) as t(kw)
             where not exists (select *
                               from whitelist wl
                               where wl.kw = t.kw)
             )
from raw_with_keyword_array ra;

I don't think it makes a big difference though.

Online example

Related Solutions

Postgresql – Select into specific array positions with array_agg()

Your answer basically gets the job done:

SELECT b.id, array_agg(b.stock) AS stock
FROM  (
   SELECT i.id, COALESCE(i_s.stock, 0) AS stock
   FROM   item i
   CROSS  JOIN unnest('{1,2}'::int[]) n
   LEFT   JOIN item_stock i_s ON i.id = i_s.item_id AND n.n = i_s.shop_id
   ORDER  BY i.id, n.n
   ) b
GROUP  BY b.id;

Two notable changes:

Order is not guaranteed without ORDER BY in the subquery or as additional clause to array_aggregate() (typically more expensive). And that's the core element of your question.
unnest('{1,2}'::int[]) instead of generate_series(1,2) as requested shop IDs will hardly be sequential all the time.

I also moved the set-returning function from the SELECT list to a separate table expression attached with CROSS JOIN. Standard SQL form, but that's just a matter of clarity and taste, not a necessity. At least in Postgres 10 or later. See:

What is the expected behaviour for multiple set-returning functions in SELECT clause?

Doing the same with LEFT JOIN LATERAL and an ARRAY constructor might be a bit faster as we don't need the outer GROUP BY and the ARRAY constructor is typically faster, too:

SELECT i.id, s.stock
FROM   item i
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT COALESCE(i_s.stock, 0)
      FROM   unnest('{1,2}'::int[]) n
      LEFT   JOIN item_stock i_s ON i_s.shop_id = n.n
                                AND i_s.item_id = i.id
      ORDER  BY n.n
      ) AS stock
   ) s;

And if you have more than just the two shops, a nested crosstab() should provide top performance:

SELECT i.id, COALESCE(stock, '{0,0}') AS stock
FROM   item i
LEFT   JOIN (
   SELECT id, ARRAY[COALESCE(shop1, 0), COALESCE(shop2, 0)] AS stock
   FROM   crosstab(
     $$SELECT item_id, shop_id, stock
       FROM   item_stock
       WHERE  shop_id = ANY ('{1,2}'::int[])
       ORDER  BY 1,2$$

     , $$SELECT unnest('{1,2}'::int[])$$
      ) AS ct (id int, shop1 int, shop2 int)
   ) i_s USING (id);

Needs to be adapted in more places to cater for different shop IDs.

PostgreSQL Crosstab Query

db<>fiddle here

Index

Make sure you have at least an index on item_stock (shop_id, item_id) - typically provided by a PRIMARY KEY on those columns. For the crosstab query, it also matters that shop_id comes first. See:

Is a composite index also good for queries on the first field?

Adding stock as another index expression may allow faster index-only scans. In Postgres 11 or later consider an INCLUDE item to the PK like so:

PRIMARY KEY (shop_id, item_id) INCLUDE (stock)

But only if you need it a lot, as it makes the index a bit bigger and possibly more susceptible to bloat from updates.

PostgreSQL JSON query array against multiple values

There is a way: combine the containment operator @> with the ANY construct:

SELECT d
FROM   grp
WHERE  d->'customers' @> ANY (ARRAY ['[{"id":"1"}]', '[{"id":"5"}]']::jsonb[]);

Or:

...
WHERE d->'customers' @> ANY ('{"[{\"id\": \"1\"}]","[{\"id\": \"5\"}]"}'::jsonb[]);

It's essential to cast the array to jsonb[] explicitly. And note that each element is a JSON array containing an object inside like the operator @> requires. So it's an array of JSON arrays.

You can use an index for this:

Index for finding an element in a JSON array

The manual explicitly states that the operator ?| is for strings matching keys or array elements only (not values):

Do any of the strings in the text array exist as top-level keys or array elements?

Best Answer

Related Solutions

Postgresql – Select into specific array positions with array_agg()

Index

PostgreSQL JSON query array against multiple values

Related Question