troubleshooting Question

How do I query JSON file from MSSQL

Avatar of Leo Torres
Leo TorresFlag for United States of America asked on
Microsoft SQL ServerJSONGoogleSQL
22 Comments1 Solution123 ViewsLast Modified:
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
   }
  ]
}


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)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\PythonStockPull\DataFiles\polygon\StockData_ESS_02-05-2020_02-13-12_PM.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON)
WITH 
(
    [ticker] varchar(20), 
    [period] varchar(20), 
    [calendarDate] Datetime, 
    [reportPeriod] Datetime, 
    [updated] Datetime, 
    [accumulatedOtherComprehensiveIncome] float, 
    [assets] float, 
    [assetsAverage] float
 
)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 22 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros