PostgreSQL: Fetch unmatched elements by comparing 2 arrays

Question

array(select
         unnest(
          array['12', '34', '56', 'NULL'])
           except select unnest(
            array['AA', 'cc', 'P4', 'G8']
        )) as modifiers

I'm getting the result array as - [NULL,56,12,34], order is messed up after comparing the 2 arrays. I want the result to be in same order of "first" array - ['12', '34', '56', 'NULL']. Is there any way to get that?

What I'm trying to do here is, I want to fetch all unmatched elements from first array in same order. In above example none of them are matching, so we are getting all the elements from first array as it is, which is correct. But it should be in same order of first array.

Another example:

array(select
             unnest(
              array['12', '34', '56', 'NULL'])
               except select unnest(
                array['AA', 'cc', 'P4', '34']
            )) as modifiers

Expected result: ['12', '56', 'NULL']

If anyone wants to do these compare operations in store procedure also. That's fine, please let me know how you will do it. — Chetan Kalore, Aug 11 '22 at 05:15

jian · Answer 1 · 2022-08-11T06:30:37.207

0

Sorting array elements

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(SELECT unnest($1) ORDER BY 1 NULLS LAST)
$$;


SELECT
    array_sort (ARRAY (
            SELECT
                unnest(ARRAY['12'::text, '34'::text, '56'::text, '111'::text, 'NULL'])
        EXCEPT
        SELECT
            unnest(ARRAY['AA', 'cc', 'P4', 'G8']))) AS modifiers;

return

{111,12,34,56,"NULL"}

demo

CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

with cte(a)
as 
(
(select
       unnest(array['12'::text, '34'::text, '56'::text,'111'::text, 'NULL']) as a)
except 
(select unnest(array['AA', 'cc', 'P4', 'G8'])))
select * from cte order by a COLLATE "numeric";

select 'a 111' <  'b 56' COLLATE "numeric" as true;

edited Aug 11 '22 at 06:30

answered Aug 11 '22 at 05:47

jian

4,119
1
17
32

I'm expecting the output here as - {12,34,56,111,NULL} - Same as order of first array. Please check the examples from my question. – Chetan Kalore Aug 11 '22 at 05:52
they are text. means that 111 is less than 56!. You can sort by numeric value. see my updates. @ChetanKalore. – jian Aug 11 '22 at 06:31

score 0 · Answer 2 · answered Aug 11 '22 at 05:55

You can use array_agg() with an order by:

select array_agg(x.i order by x.idx)
from unnest(array['12', '34', '56', 'NULL']) with ordinality as x(i,idx)
where not exists (select * 
                 from unnest(array['AA', 'cc', 'P4', '34']) as t(e)
                 where t.e = x.i);

PostgreSQL: Fetch unmatched elements by comparing 2 arrays

2 Answers2