Thread: 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 ?
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Condor > Sent: Wednesday, August 01, 2012 4:16 PM > To: pgsql-general@postgresql.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 yourcase your array will have NULL "values" when phone numbers are missing but the upper bound will still show a positivenumber. 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 numbersare 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 situationresults 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, thecurrent 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 whateveris stored in the source column. David J.
On 2012-08-01 23:59, David Johnston wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Condor
>> Sent: Wednesday, August 01, 2012 4:16 PM
>> To: pgsql-general@postgresql.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.
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Condor
> Sent: Thursday, August 02, 2012 4:35 AM
> To: pgsql-general@postgresql.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@postgresql.org [mailto:pgsql-general-
> >> owner@postgresql.org] On Behalf Of Condor
> >> Sent: Wednesday, August 01, 2012 4:16 PM
> >> To: pgsql-general@postgresql.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
conditionis 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
presentotherwise 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.
On 2012-08-02 21:32, David Johnston wrote:
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Condor
>> Sent: Thursday, August 02, 2012 4:35 AM
>> To: pgsql-general@postgresql.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@postgresql.org [mailto:pgsql-general-
>> >> owner@postgresql.org] On Behalf Of Condor
>> >> Sent: Wednesday, August 01, 2012 4:16 PM
>> >> To: pgsql-general@postgresql.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.
Very interesting,
it's very useful.
Thank you.