Get all array_agg() values with one matched value Postgres

Question

I want to get all array_agg() values with one matched value.

Like if in robotsession."Session_OS" there are multiple values ['Android', 'IOS', 'Windows']

In my current query it's giving ['Android'] only. But I want all.

My query is:

SELECT robotAds."Ad_ID",
    ARRAY_AGG(DISTINCT quote_literal(robotSession."Session_OS"))
    as Session_OS
    FROM robot__ads robotAds LEFT JOIN
    robot__session__scraper__data robotScraper ON
    robotScraper."adIDAdID" = robotAds."Ad_ID" LEFT JOIN
    robot__session_data robotSession ON robotSession."id" = robotScraper."sessionIDId" WHERE
    robotScraper."sessionIDId" IS NOT NULL
    AND robotsession."Session_OS" IN ('Android')
    GROUP BY robotAds."Ad_ID"

In the result of this query is "Session_OS" gives only 'Android' value.

And sample data of this query is like:

  id   | Session_OS
-------|-------------
 641   | {'Android'}
 642   | {'Android'}
 643   | {'Android'}

But I want all the values that are IN Session_Os Agregation if it matched any of one. like here in my query it is matching with 'Android':

  id   | Session_OS
-------|-------------
 641   | {'Android, 'IOS'}
 642   | {'Android, 'Windows'}
 643   | {'Android', 'IOS', 'Windows'}

How I can achieve this ??

UPDATE:

Full data, without this condition in query AND robotsession."Session_OS" IN ('Android')

  id   | Session_OS
-------|-------------
 641   | {'Android, 'IOS'}
 642   | {'Android, 'Windows'}
 643   | {'Android', 'IOS', 'Windows'}
 644   | {'IOS', 'Windows'}
 645   | {'IOS'}

But after adding that condition AND robotsession."Session_OS" IN ('Android') in query result is

   id  | Session_OS
-------|-------------
 641   | {'Android'}
 642   | {'Android'}
 643   | {'Android'}

But I want this. How I can do that?

  id   | Session_OS
-------|-------------
 641   | {'Android, 'IOS'}
 642   | {'Android, 'Windows'}
 643   | {'Android', 'IOS', 'Windows'}

a) Please minimize your example code to the absolut necessary parts which are relevant to describe your problem. I.e. I guess, it's not necessary to show us 3 or 4 joins. b) Please add some sample data and the expected output, so that we could reproduce your problem — S-Man
– S-Man, Commented Dec 14, 2020 at 14:14
Hi Thanks for quick reply. I have updated my question. Kindly check again. — Aman Sharma
– Aman Sharma, Commented Dec 14, 2020 at 15:26
what is quote_literal(). Please show the data before grouping — S-Man
– S-Man, Commented Dec 14, 2020 at 16:06
quote_literal() Is nothing. You can ignore it. It is just add quote ('') to each value. — Aman Sharma
– Aman Sharma, Commented Dec 14, 2020 at 16:18

S-Man · Accepted Answer · 2020-12-14 17:28:07Z

2

demo:db<>fiddle

SELECT
    id,
    ARRAY_AGG(session_os)
FROM
    t
GROUP BY id
HAVING ARRAY_AGG(session_os) && ARRAY['Android']

Your problem is that you FIRST filter the records with session_os = Android. And afterwards you are aggregate these.

You have to aggregate first and then have a look into the array aggregate if Android is an element. This can be done using the HAVING clause and the && operator which returns true if two arrays contain the same element(s).

answered Dec 14, 2020 at 17:28

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aman Sharma Over a year ago

Hi, Thanks This is working. There is one more issue. What if there is a null value in ARRAY_AGG(session_os) ?. It's throwing an error. ERROR: array must not contain nulls SQL state: 22004

Aman Sharma Over a year ago

Hi, Thank This is working. There is one more issue. What if there is a null value in ARRAY_AGG(session_os) ?. It's throwing an error. ERROR: array must not contain nulls SQL state: 22004

S-Man Over a year ago

You can easily add a "WHERE session_os IS NOT NULL" filter before grouping: dbfiddle.uk/…

Aman Sharma Over a year ago

Why it's not allowing me to get NULL values as well ? If I am using "WHERE session_os IS NULL" and HAVING ARRAY_AGG(session_os) && ARRAY['Android']. It againg throwing same error. I can't do this. I can't get NULL values ?

Aman Sharma Over a year ago

See this dbfiddle.uk/… . My case is like this... in this case how I can get null values as well.

|

Collectives™ on Stack Overflow

Get all array_agg() values with one matched value Postgres

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related