2

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:

2
  • "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"?
    – user1822
    Feb 4, 2021 at 12:32
  • 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)
    – nskalis
    Feb 5, 2021 at 18:21

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.