PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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 query but I have no clue how to do it in same query:

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

In input

  • r stands for table,
  • v stands for view
  • f stands for function

and in output I need to filter based on r so that I will get all the table records but not sure how to achieve it using above query.Can anyone please help.

Output:

(a,b,**r**,)
(c,d,**r**,)

I have tried to apply 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 hence need help with this.

Tags:

2

Answers


  1. 0

    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:

    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: 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)
where arr.element<>'table_name_to_be_skipped';

    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)
                               ,'table_name_to_be_skipped'))
from mgmt.log_metrics as a;
    Login or Signup to reply.
  2. 0

    Asssuming that your filter condition should be on the last element of an array ("… Also tried Right function …") maybe this could help you find your way out of this:

    WITH    --  S a m p l e    D a t a :
  tbl AS
    ( Select 1 as id, Array['a', 'b', '**r**'] as arr_data Union All
      Select 2 as id, Array['c', 'd', '**r**'] as arr_data Union All
      Select 3 as id, Array['e', 'l', 'v'] as arr_data Union All
      Select 4 as id, Array['g', 'h', 'i', 'f'] as arr_data 
    )

    --      S Q L :
SELECT   f.id, f.arr_data
FROM   ( Select   e.id, e.arr_data, 
                  Sum(1) Over(Partition By e.id Order By e.id Rows Between Unbounded Preceding And Current Row) as rn, 
                  e.element,
                  e.arr_len
         From   ( Select  id, arr_data, 
                          unnest( arr_data ) as element, 
                          array_length( arr_data, 1) as arr_len
                  From tbl
               ) e
      ) f
WHERE  f.arr_len = f.rn And -- this is last element of arr_data 
       f.element = '**r**'  -- adjust this condition to your actual data and context

    /*    R e s u l t :
id  arr_data
--  ----------------
 1  {a,b,**r**}
 2  {c,d,**r**}     */

    See dbfiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search