search

godfriedmeesters / scraper

As part of DiffScraper, one or more bots can be deployed. Ready-to-use bots are provided that can extract offers from mobile applications, mobile websites and desktop websites.
GNU General Public License v3.0
2 stars 0 forks source link

Interesting queries #18

Open godfriedmeesters opened 3 years ago

godfriedmeesters commented 3 years ago

Let's collect interesting queries here. Now you will also understand why I put the comparisons in SQL.

godfriedmeesters commented 3 years ago

Select price differences between Opodo Web and Opodo App for a given flight:

SELECT sr."comparisonId", sr."id" AS scraperRunId, s.name, sr."startTime", srr."result" ->> 'price'AS price, srr."result" ->> 'origin' AS origin, srr."result" ->> 'destination' AS destination, srr."result" ->> 'departureTime' AS departureTime, srr."result" ->> 'arrivalTime' AS arrivalTime, srr."result" ->> 'airline' AS airline, scraper -> 'params' as scraperparams, srr."result" -> 'screenshot' as screenshot from "scraper" AS s INNER JOIN "scraperRun" AS sr ON s.id = sr."scraperId" INNER JOIN comparison comp ON sr."comparisonId" = comp.id INNER JOIN "scraperRunResult" AS srr ON sr.id = srr."scraperRunId", jsonb_array_elements(comp."comparisonConfig"->'scrapers') AS scraper WHERE sr."startTime" > '2021-04-23' AND sr."comparisonId" = 68 AND scraper->>'scraperClass' = s.name -- LIMIT to certain tickets AND srr.result->>'airline' = 'Lufthansa' AND srr.result->>'departureTime' = '12:25' AND srr.result->>'arrivalTime' = '13:40' -- LIMIT to certain tickets ORDER BY sr."id"

godfriedmeesters commented 3 years ago

Note the ->> operator to select an object as string