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: https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key ) — Dai, Mar 02 '21 at 05:43
Your senior _should_ have changed it to a separate table. Is it possible to make DB design changes at this point? — Dai, Mar 02 '21 at 06:05
if i could replace this NOT IN with a join that too will be helpful — a_wolf_with_no_master, Mar 02 '21 at 06: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 https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names — , Mar 02 '21 at 06:16
as the way im updating them they will not be fetched in result anymore.. anyways, problem is that 'NOT IN' and subquery. those are too slow — a_wolf_with_no_master, Mar 02 '21 at 06:22
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](https://creativecommons.org/licenses/by-sa/4.0/), 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?](/help/what-to-do-instead-of-deleting-question) — cigien, Mar 04 '21 at 11:41

Dai · Answer 1 · 2021-03-02T06:35:30.880

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 );

score 0 · Accepted Answer · answered Mar 02 '21 at 19:17

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

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

2 Answers2