postgresql – check if a list of strings in a jsonb contains (or not) an array of strings

Given a table “bgp”.”interconnect”:

the query 1) returns

SELECT array_to_json(array_agg(netelements)) FROM (SELECT jsonb_object_keys("bgp"."interconnect"."pulse") AS netelements FROM "bgp"."interconnect" WHERE "bgp"."interconnect"."asn" = 7224) x;
("de-fra02a-ri1","nl-ams09c-ri1","uk-lon01b-ri1")

the query 2) returns

SELECT array_agg(netelements) FROM (SELECT jsonb_object_keys("bgp"."interconnect"."pulse") AS netelements FROM "bgp"."interconnect" WHERE "bgp"."interconnect"."asn" = 7224) x;
{de-fra02a-ri1,nl-ams09c-ri1,uk-lon01b-ri1}

Given a table “ip”.”aws_inet”:

the query 3)

SELECT "ip"."aws_inet"."pulse"
FROM "ip"."aws_inet"
WHERE "ip"."aws_inet"."service" = 'S3' AND "ip"."aws_inet"."is_ok" IS true
AND "ip"."aws_inet"."metadata" ->> 'region' = 'eu-central-1'

returns

("de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1")
("nl-ams09c-ri1", "uk-lon01b-ri1")
...
("uk-lon01b-ri1")

I would like to check

  • using/against the reference result set from the query 1) or 2)
  • which strings in "ip"."aws_inet"."pulse" jsonb column do belong to the reference result set
  • which strings in "ip"."aws_inet"."pulse" jsonb column do not belong to the reference result set

Can you please advise on how to achieve this?

I have tried (in vain) the following query as a playground (it errors):

-- SELECT "ip"."aws_inet"."pulse", "ip"."aws_inet"."prefix" AS "prefix", "ip"."aws_inet"."service" AS "service", "ip"."aws_inet"."metadata" AS "metadata", "ip"."aws_inet"."is_ok" AS "is_ok", "ip"."aws_inet"."updated_at" AS "updated_at", "ip"."aws_inet"."inserted_at" AS "inserted_at"
-- FROM "ip"."aws_inet"
-- WHERE "ip"."aws_inet"."service" = 'S3' AND "ip"."aws_inet"."is_ok" IS true
-- AND "ip"."aws_inet"."metadata" ->> 'region' = 'eu-central-1'
-- AND "ip"."aws_inet"."pulse" ?| ARRAY('nl-ams09c-ri1','uk-lon01b-ri1')

inspired from