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

• user1822: "ip"."aws_inet"."pulse" ?| ARRAY['nl-ams09c-ri1','uk-lon01b-ri1'] should do what you want (test if the keys are present in the JSON). What is the error you get? And what exactly is the "reference result set"?
• nskalis: thank you @a_horse_with_no_name for looking into it, it turned out it was the client's problem and the query works. I would appreciate your help in making the query more flexible/dynamic (see EDIT)