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"? – a_horse_with_no_name Feb 4 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 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.