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

Question

Given a table "bgp"."interconnect":

Query 1

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;

returns

                   array_to_json                   
---------------------------------------------------
 ["de-fra02a-ri1","nl-ams09c-ri1","uk-lon01b-ri1"]
(1 row)

Query 2

SELECT array_agg(netelements) 
FROM (
  SELECT jsonb_object_keys("bgp"."interconnect"."pulse") AS netelements 
  FROM "bgp"."interconnect" 
  WHERE "bgp"."interconnect"."asn" = 7224
) x;

returns

                  array_agg                  
---------------------------------------------
 {de-fra02a-ri1,nl-ams09c-ri1,uk-lon01b-ri1}
(1 row)

Given a table "ip"."aws_inet":

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

                        pulse                        
-----------------------------------------------------
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
(11 rows)

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"
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']

EDIT: the above works.. it turned out that is a client's problem :(

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'
AND     "ip"."aws_inet"."pulse" ?| ARRAY['nl-ams09c-ri1','uk-lon01b-ri1'] ;
                        pulse                        
-----------------------------------------------------
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
 ["de-fra02a-ri1", "nl-ams09c-ri1", "uk-lon01b-ri1"]
(11 rows)

How can I re-write the last query so that I can pass the SELECT statement from query 1 (instead of hard-coding ARRAY['nl-ams09c-ri1','uk-lon01b-ri1']) ? Conceptually, something like this:

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'
AND     "ip"."aws_inet"."pulse" ?| 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;

I was inspired by the following articles:

• PostgreSQL JSON query array against multiple values

• PostgreSql JSONB SELECT against multiple values

• https://www.postgresql.org/docs/current/functions-json.html

• https://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string