postgresqlpostgresql-9.4

How to filter out some records while unnest in the query


I am trying to find out the way to filter out records based on a query but I have no clue how to do it in same statement:

Query:

select unnest(array_cat(a.object_references, a.dependent_object_references)) 
from mgmt.log_metrics a

Input:
Select will provide records in below format:

{a,b,r}
{c,d,r}
{e,l,v}
{g,h,i,f}

and in output I need to filter based on r so that I will get all the table records. Not sure how to achieve it using above query.

Output:

{a,b,r}
{c,d,r}

I have tried to apply a where clause and also tried to put substring() but after doing research found that substring() will not work directly with unnest(). Also tried right() function but I am new so not sure how to achieve this.


Solution

  • Calling a table function or a set-returning function like unnest() in the select list is pretty common, but that's more of a syntactic shortcut rather than a normal place for them. Your code effectively does this:
    demo at db<>fiddle

    select arr.element
    from mgmt.log_metrics as a
    cross join lateral unnest(array_cat( a.object_references
                                        ,a.dependent_object_references)) as arr(element)
    

    When you structure it like that, it becomes a bit clearer that you can simply add a where to filter out some of the elements resulting from unnesting the concatenated arrays:

    select arr.element
    from mgmt.log_metrics as a
    cross join lateral unnest(array_cat( a.object_references
                                        ,a.dependent_object_references)) as arr(element)
    where arr.element='r';
    

    If you prefer to modify the array before unnesting it, you can use array_remove():

    select id, unnest(array_remove( array_cat( a.object_references
                                              ,a.dependent_object_references)
                                   ,'r'))
    from mgmt.log_metrics as a;
    

    If you were exploding the array simply to access individual elements in order to do your search/filtering, but on output you need the arrays whole again, you can drop the unnest() entirely and use the @> containment operator:

    select b.arr
    from mgmt.log_metrics as a
    cross join lateral array_cat( a.object_references
                                 ,a.dependent_object_references) as b(arr)
    where b.arr @> array['r'];
    

    In case you also need to consider the position of an element in the array, you can use square brackets [] for subscripting. Regular SQL arrays in Postgres don't support negative subscripts to check the last element, 2nd from last, etc, but you can operate relative to array_length() to achieve that:

    select a.id, b.arr
    from mgmt.log_metrics as a
    cross join lateral array_cat( a.object_references
                                 ,a.dependent_object_references) as b(arr)
    where b.arr[array_length(b.arr,1)] = 'r';
    

    And if you feel like its handy to keep the positions around, or you'd like to know them when you unnest() for something else, you can use with ordinality (as with any SRF):

    select u.position, u.element
    from mgmt.log_metrics as a
    cross join lateral array_cat( a.object_references
                                 ,a.dependent_object_references) as b(arr)
    cross join lateral unnest(b.arr) WITH ORDINALITY as u(element,position)
    where u.element='r' 
      and u.position=array_length(b.arr,1);