CODEWITHSUNDEEP
sqlpostgresqljsonb
nskalis
nskalis

Reputation: 2382

how to compare an array element of a jsonb column

I am having a difficult finding out how I can extract the value of an element of an array in a jsonb column and make a comparison.

In other words, I am trying to find out if proto_nexthop (is a key in a jsonb column that holds a list of IP addresses) IP address belongs to the subnet 84.116.198.0/23.

SELECT * 
FROM vpn.route_targets 
WHERE is_ok=true 
  AND country='INTERNATIONAL' 
  AND INET ANY(SELECT jsonb_array_elements(pulse -> 'proto_nexthop'))::TEXT << INET '84.116.198.0/23' ;

Could you advise please what I am doing wrong and how to accomplish that ?

Example records:

# SELECT route_targets.value, pulse -> 'proto_nexthop' FROM vpn.route_targets WHERE is_ok=true AND country='INTERNATIONAL'  LIMIT 3 ;
     value      |                                                  ?column?                                                  
----------------+------------------------------------------------------------------------------------------------------------
 0101:933365229 | ["84.116.198.x"]
 0101:933365141 | ["84.116.x.x", "84.116.x.x", "84.116.x.x", "84.116.x.x", "84.116.x.x", "84.116.x.x"]
 0101:933365016 | ["84.116.x.x", "84.116.x.x"]
(3 rows)

Desired output:

     value      |                                                  ?column?                                                  
----------------+------------------------------------------------------------------------------------------------------------
 0101:933365229 | ["84.116.198.x"]

Upvotes: 0

Views: 47

Answers (1)

user330315
user330315

Reputation:

You need to unnest the elements from the array and then use that in an EXISTS operator:

SELECT rt.value, rt.pulse -> 'proto_nexthop' 
FROM route_targets rt
WHERE is_ok
  AND country='INTERNATIONAL' 
  AND exists (select *
              from jsonb_array_elements_text(rt.pulse -> 'proto_nexthop') as x(ip)
              where x.ip::inet << INET '84.116.198.0/23')

Upvotes: 1

Related Questions