Advertisement
rgruber

asn neighbours table (sql)

Jul 3rd, 2022
1,348
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 0.86 KB | None
  1. -- SELECT ts,JSON_value(jdoc,CONCAT('$.names.',SUBSTRING_INDEX(k,':',-1))) _name, cast(SUBSTRING_INDEX(k,':',-1) AS  UNSIGNED) asn,rid FROM bh_cache WHERE k LIKE 'as-name:%'  oRDER BY 4 DESC;
  2. -- SELECT rid,ts,r.*  FROM bh_cache a ,json_table(jdoc,'$' COLUMNS( holder VARCHAR(256) path '$.holder', announced BOOL path '$.announced', asn int UNSIGNED path '$.resource', lastChecked DATE path '$.query_endtime'   ) )r  WHERE k LIKE 'as-overview:%' ORDER BY 5 DESC;
  3. SELECT * FROM (
  4. sELECT rid,ts,cast(json_value(jdoc,'$.resource') AS UNSIGNED) asn, r.*, JSON_VALUE(jdoc,'$.query_endtime')lastChecked FROM bh_cache a , json_table(jdoc,'$.neighbours[*]' COLUMNS( peer int UNSIGNED path '$.asn', TYPE VARCHAR(15) path '$.type',POWER INT path '$.power',v4 INT path '$.v4_peers' ,v6 INT path '$.v6_peers' ) )r  WHERE k LIKE 'asn-neighbours:%'
  5. ) a WHERE asn=8075
  6. ORDER BY type,v4 desc
  7.  
  8.  
Advertisement
RAW Paste Data Copied
Advertisement