How to use array in a sql join and check if all of the array elements satisfy a condition?

Question

I have two tables, activity and contacts in postgresql.

An activity can have multiple contacts in array form, like this

contact Id = {23,54,34}.

I want to delete a activity only if all the contact Ids of that activity are deleted in contacts table and keep the activity if at least one one contact id is still not deleted.

Deleted At is column in contacts table to check for deleted contacts. I don't want to use NOT IN.

Activity table

id    contact Id 
-------------------    
16    {60,61,23}
15    {}        
5     {59}
6     {}

Is there a reason why you're using an inline array instead of a proper foreign-key and mapping table? (See here: stackoverflow.com/questions/41054507/… ) — Dai
– Dai, Commented Mar 2, 2021 at 5:43
Your senior should have changed it to a separate table. Is it possible to make DB design changes at this point? — Dai
– Dai, Commented Mar 2, 2021 at 6:05
if i could replace this NOT IN with a join that too will be helpful — a_wolf_with_no_master
– a_wolf_with_no_master, Commented Mar 2, 2021 at 6:16
This is a really bad design and the problems you have are a direct result of that bad design. And why are you using an UPDATE if you want to DELETE the rows in activity? Btw you should also avoid those dreaded quoted identifiers wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Mar 2, 2021 at 6:16
Please don't vandalize your posts. By posting on the Stack Exchange network, you've granted a non-revocable right, under the CC BY-SA 4.0 license, for Stack Exchange to distribute that content (i.e. regardless of your future choices). By Stack Exchange policy, the non-vandalized version of the post is the one which is distributed, and thus, any vandalism will be reverted. If you want to know more about deleting a post please see: How does deleting work? — cigien
– cigien, Commented Mar 4, 2021 at 11:41

astentx · Accepted Answer · 2021-03-02 19:17:32Z

1

You can use simple EXISTS predicate testing contacts table with activity.contacts array:

create table activity (
  id int primary key,
  contacts int[]
)

create table contacts (
  id int primary key,
  name varchar(10),
  deleted boolean
)

insert into activity
  select 16 as id, '{1,2,3}'::int[] as contacts union all
  select 15, null union all      
  select 5, '{4}' union all
  select 6, '{6, 5}'

insert into contacts
  select 1 as id, 'q' as name, false as deleted union all
  select 2, 'w', false union all
  select 3, 'e', true union all
  select 4, 'r', false union all
  select 5, 't', true union all
  select 6, 'y', true

delete
from activity a
where not exists (
  select null
  from contacts c
  where not(c.deleted)
    and c.id = any(a.contacts)
)

2 rows affected

db<>fiddle here

answered Mar 2, 2021 at 19:17

astentx

6,7812 gold badges19 silver badges26 bronze badges

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

Comments

Dai · Accepted Answer · 2021-03-02 06:35:30Z

Use UNNEST to convert the nested-array to rows, then do an anti-join to look for broken references:

(An anti-join is when you perform a x LEFT OUTER JOIN y with a WHERE y.KeyColumn IS NULL - this gives you the opposite of an INNER JOIN with the same join criteria).

WITH unnested AS (

    SELECT
        Id AS ActivityId,
        UNNEST( ContactId ) AS ContactIdFromArray
    FROM
        crmActivity
)
SELECT
    u.ActivityId,
    u.ContactIdFromArray AS Missing_ContactId_in_Activity
FROM
    unnested AS u
    LEFT OUTER JOIN contacts AS c ON
        c.ContactId = u.ContactIdFromArray
WHERE
    c.ContactId IS NULL
ORDER BY
    u.ActivityId

I want to delete a activity only if all the contact Ids of that activity are deleted in contacts table and keep the activity if at least one one contact id is still not deleted.

This can be done with a DELETE FROM using WHERE crmActivity.Id IN with a CTE that generates the correct set of bad crmActivity.Id values, via a GROUP BY with the above query:

WITH unnested AS (

    SELECT
        Id AS ActivityId,
        UNNEST( ContactId ) AS ContactIdFK
    FROM
        crmActivity
)
WITH brokenContacts AS (
    
    SELECT
        u.ActivityId,
        u.ContactIdFK,
        c.ContactId AS ContactIdPK
        
    FROM
        unnested AS u
        LEFT OUTER JOIN contacts AS c ON
            c.ContactId = u.ContactIdFromArray
)
WITH counts AS (
    
    SELECT
        ActivityId,
        COUNT(*) AS ContactIdCount,
        COUNT( CASE WHEN ContactIdPK IS NULL THEN 1 END ) AS BadContactIdCount
    FROM
        brokenContacts
    GROUP BY
        ActivityId
)
WITH badActivityIds AS (
    
    SELECT
        ActivityId
    FROM
        counts
    WHERE
        BadContactIdCount = ContactIdCount
        AND
        ContactIdCount > 0
)
DELETE FROM
    crmActivity
WHERE
    ActivityId IN ( SELECT ActivityId FROM badActivityIds );

Collectives™ on Stack Overflow

How to use array in a sql join and check if all of the array elements satisfy a condition?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related