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. — CAK
– CAK, Commented Aug 11, 2022 at 5:15

user330315 · Accepted Answer · 2022-08-11 05:55:19Z

0

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);

answered Aug 11, 2022 at 5:55

user330315

Sign up to request clarification or add additional context in comments.

Comments

jian · Accepted Answer · 2022-08-11 06:30:37Z

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, 2022 at 6:30

answered Aug 11, 2022 at 5:47

jian

5,0131 gold badge29 silver badges46 bronze badges

2 Comments

CAK Over a year ago

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.

jian Over a year ago

they are text. means that 111 is less than 56!. You can sort by numeric value. see my updates. @ChetanKalore.

Collectives™ on Stack Overflow

PostgreSQL: Fetch unmatched elements by comparing 2 arrays

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related