alexa
Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry

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]

32 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