Avatar of Leo Torres
Leo Torres
Flag for United States of America

asked on 

How do I query JSON file from MSSQL

How can I query a json file of this format

if yuo notice this has this status header I want to query whats inside the results

  "status": "OK",
  "count": 1,
  "results": [
      "ticker": "AAPL",
      "period": "Q",
      "calendarDate": "2019-03-31",
      "reportPeriod": "2019-03-31",
      "updated": "1999-03-28",
      "accumulatedOtherComprehensiveIncome": 0,
      "assets": 0,
      "assetsAverage": 0

Open in new window

I got this query below that doe not fail but it returns null for all the columns. Not even sure i know how i would ask this question on google.

Declare @JSON varchar(max)
FROM OPENROWSET (BULK 'C:\PythonStockPull\DataFiles\polygon\StockData_ESS_02-05-2020_02-13-12_PM.json', SINGLE_CLOB) import
    [ticker] varchar(20), 
    [period] varchar(20), 
    [calendarDate] Datetime, 
    [reportPeriod] Datetime, 
    [updated] Datetime, 
    [accumulatedOtherComprehensiveIncome] float, 
    [assets] float, 
    [assetsAverage] float

Open in new window

Microsoft SQL ServerJSONGoogleSQL

Avatar of undefined
Last Comment
Ryan Chong

8/22/2022 - Mon