Re: Need help with SQL query and finding NULL array_agg

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Condor
> Sent: Thursday, August 02, 2012 4:35 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Need help with SQL query and finding NULL
> array_agg
>
> On 2012-08-01 23:59, David Johnston wrote:
> >> -----Original Message-----
> >> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> >> owner(at)postgresql(dot)org] On Behalf Of Condor
> >> Sent: Wednesday, August 01, 2012 4:16 PM
> >> To: pgsql-general(at)postgresql(dot)org
> >> Subject: [GENERAL] Need help with SQL query and finding NULL
> >> array_agg
> >>
> >> Hello ppl,
> >>
> >> I have a problem with one sql query, can some one help me. My query
> >> is:
> >>
> >> SELECT array_agg(month) AS month, array_agg(status) AS status,
> >> array_agg(service) AS service, case when
> >> array_upper(array_agg(phone),
> >> 1) is not null THEN array_agg(phone) else array_agg(mobile) END FROM
> >> bills WHERE status > 1 GROUP BY mobile
> >>
> >> I try with simple query to identify did array_agg(phone) is empty or
> >> null and if is it to return me mobile field, if not empty to return
> >> me phone.
> >> Mobile field
> >> exist always, but phone may exists for that mobile may not exists.
> >> One mobile can have few services like:
> >>
> >>
> >> mob service phone
> >> 1321543434 64
> >> 1321543434 66 13255555
> >>
> >>
> >> I try few thing but sql only return me records that phone is not
> >> empty, but I need them both.
> >>
> >> Any one has ideas what I can do ?
> >
> > ARRAY_AGG() is never an empty array since there is always at least a
> > single record that is going to be aggregated. In your case your array
> > will have NULL "values" when phone numbers are missing but the upper
> > bound will still show a positive number.
> >
> > SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1
> >
> > You would need to write a custom aggregation that ignores NULL and
> > thus could return an empty array if no valid phone numbers are
> > present.
> >
> > The proper logic would be:
> >
> > CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL
> THEN
> > ... END
> >
> > You also likely want to use:
> >
> > ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only
> > present a single time and ensure that an all-NULL situation results
> > in a single element instead of one NULL for each input record.
> >
> > Hopefully this help because I couldn't make heads nor tails as to what
> > exactly your issue is. The lack of input data, the current output,
> > and the desired output limits my ability to understand and help.
> >
> > One last comment: I would generally avoid naming the output of an
> > ARRAY_AGG(column) the same name as the input column. I generally, at
> > minimum, make the output column name plural to reflect the fact that
> > it contains multiple values of whatever is stored in the source
> > column.
> >
> > David J.
>
>
> Hello,
> I understand what is the problem, but I can't combine your example with my
> case.
>
> I write my example in middle of the night and may be I miss to say explain
> much more about the structure:
>
>
> mob service phone month
> 132999 64 1
> 1321543434 66 13255555 1
> 1321543434 67 13255555 2
>
> First record when phone is empty and mob is 132999 the number is
> correct.
> Second two records also is correct, but the phone is not empty so I
> need
> that filed phone, they have services and month when to start.
> I'm unable to use phone_agg[1] IS NULL because sql return me error
> that can't
> use the phone_agg[1]
>
> Im expect that result:
> month | status | service | array_agg
> ---------+---------+---------+---------------------------
> {07} | {0} | {64} | {132999}
> {08,07} | {0,0} | {66,67} | {13255555,13255555}
>
>
> In the end I will have arrays for every phone which service will use.
>
>

Hopefully this will help.

The first thing I did was break up the query into parts
0) data
1) aggregation
2) conditional return

Note I am using the ability for the CTE to provide column names so the contained queries are not cluttered with "AS alias" constructs.

In order to make things simpler I avoid storing NULL in the "phones" array and instead store "N/A" if the phone is missing. This lets me use " op ANY/ALL(array)" later on to check on the contents of the array. The result of that condition is called "final_phones" and it either matches the "mobs" or the "phones" array depending on whether all of the "phone" numbers are missing.

Another option is to use the "masterphones" array where the value stored into the array is the "phone" number if present otherwise it is the "mob" number.

WITH data (mob, service, phone, mth) AS (
VALUES
('132999','64',NULL,'1')
, ('132999','65','12345','1')
, ('1321543434','66','13255555','1')
, ('1321543434','67','13255555','2')
)
, maingroup (mob, mobiles, services, phones, months, masterphones) AS (
SELECT mob, array_agg(mob), array_agg(service), array_agg(COALESCE(phone,'N/A')), array_agg(mth), array_agg(COALESCE(phone, mob))
FROM data
GROUP BY mob
)
SELECT *, CASE WHEN 'N/A' = ALL(phones) THEN mobiles ELSE phones END AS final_phones
FROM maingroup

David J.