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']

CodePudding user response：

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

CodePudding user response：

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;