sql - Crawl stock availability data from website

Question

I want to crawl the stock availability of a certain product from the following website.

[{"@type":"Offer","availability":"https://schema.org/InStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2756&spec[]=285"},{"@type":"Offer","availability":"http://schema.org/OutOfStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2768&spec[]=285"},{"@type":"Offer","availability":"http://schema.org/OutOfStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2811&spec[]=285"},{"@type":"Offer","availability":"http://schema.org/OutOfStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2757&spec[]=285"}],"aggregateRating":{"@type":"AggregateRating","ratingValue":"9.0","ratingCount":"6","bestRating":"10"}}

I need the schema.org/Instock or schema.org/OutOfStock to eventually get a notification when the product in on stock so I can buy it. It is for me personal since availability of Mountainbikes is very limited at the moment. So I want to build a quick program to get a notification when mount MTB size in on stock If I have a script to get the data for this specific product I can make a ssis with sql server and set an email notification when the "outstock" field is "instock". I am familiar with SSIS and SQL server. Could somebody help me get the data fetched from the website?

question from:https://stackoverflow.com/questions/65935716/crawl-stock-availability-data-from-website

Answer 1

You can do json directly in SSIS, but you can also use SQL Server

Insert the Json in a table using ssis, and then parse it for example using Openjson:

Here I insert your example json in a temp table, and query it using tsql:

DECLARE @json NVARCHAR(MAX) =
N'
[{"@type":"Offer","availability":"https://schema.org/InStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2756&spec[]=285"}
,{"@type":"Offer","availability":"http://schema.org/OutOfStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2768&spec[]=285"}
,{"@type":"Offer","availability":"http://schema.org/OutOfStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2811&spec[]=285"}
,{"@type":"Offer","availability":"http://schema.org/OutOfStock","price":"479.00","priceCurrency":"EUR","url":"https://www.mantel.com/cube-aim-pro&spec[]=9470&spec[]=2757&spec[]=285"}]
,"aggregateRating":{"@type":"AggregateRating","ratingValue":"9.0","ratingCount":"6","bestRating":"10"}}'


CREATE TABLE #tmp (
      id INT IDENTITY (1, 1) NOT NULL
    , json NVARCHAR(MAX) NOT NULL
)

INSERT INTO #tmp (json)
VALUES (@json)

SELECT [AdType]
     , [availability]
     , [price]
     , [priceCurrency]
     , [url]
FROM (
    SELECT TOP 1 json
    FROM #tmp
    ORDER BY id DESC
) a
    OUTER APPLY OPENJSON(a.json)
    WITH
    (
    AdType VARCHAR(100) '$."@type"'
    , availability NVARCHAR(256)
    , price DECIMAL(19, 2)
    , priceCurrency NVARCHAR(3)
    , url NVARCHAR(512)
    )

You have python in your tags. If you are using Python to fetch the data you can just parse the json into a python object directly without using SSIS or SQL server however