ドメインを含むあらゆるものを、 JSONLファイル1つで強化します

SELECT "TLD", count(*) AS domains
FROM read_json('webatla-all-data-2026-08-18.jsonl.zst',
       format='newline_delimited', compression='zstd',
       columns={'TLD':'VARCHAR', 'Technologies':'JSON'})
WHERE list_contains(
        CASE json_type("Technologies")
          WHEN 'ARRAY'  THEN json_extract_string("Technologies", '$[*]')
          WHEN 'OBJECT' THEN json_keys("Technologies")
          ELSE []::VARCHAR[] END, 'WordPress')
GROUP BY "TLD" ORDER BY domains DESC LIMIT 20;

SELECT c.*, w."Country by IP", w."PR value", w."Registrar"
FROM 'crm.csv' c
LEFT JOIN (
  SELECT "Domain", "Country by IP", "PR value", "Registrar"
  FROM read_json('webatla-all-data-2026-08-18.jsonl.zst',
         format='newline_delimited', compression='zstd',
         columns={'Domain':'VARCHAR', 'Country by IP':'VARCHAR',
                  'PR value':'DOUBLE', 'Registrar':'VARCHAR'})
) w ON lower(c.domain) = w."Domain";

import io, json, zstandard as zstd

with open("webatla-all-data-2026-08-18.jsonl.zst", "rb") as f:
    for line in io.TextIOWrapper(zstd.ZstdDecompressor().stream_reader(f)):
        r = json.loads(line)
        t = r.get("Technologies") or []
        names = list(t) if isinstance(t, dict) else t
        if "WordPress" in names and (r.get("DNS records") or {}).get("MX"):
            print(r["Domain"], r.get("Registrar"))