alexa

Where/conditional statement when using array_agg in SQL ?

Where/conditional statement when using array_agg in SQL ?

Combination of array_except and cardinality should do the trick:

array_except(x, y) → array
Returns an array of elements in x but not in y, without duplicates.

cardinality(x) → bigint
Returns the cardinality (size) of the array x.

 -- sample data
WITH dataset (customer_id, transaction_id_2021, transaction_id_2022) AS (
    VALUES (382, array['81-a-39-b','22-t-42-f'], array['81-a-39-b','22-t-42-f','93-g-64-t']),
        (742, array['53-y-11-g','43-t-55-a'], array['53-y-11-g','43-t-55-a'])
) 

-- query
select *
from dataset
where cardinality(array_except(transaction_id_2022, transaction_id_2021)) > 0 

Output:

customer_id transaction_id_2021 transaction_id_2022
382 [81-a-39-b, 22-t-42-f] [81-a-39-b, 22-t-42-f, 93-g-64-t]

270 0
7

Write a Comments


* Be the first to Make Comment

GoodFirms Badge
GoodFirms Badge

Fix Your Meeting With Our SEO Consultants in India To Grow Your Business Online

Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry