Link to home
Start Free TrialLog in
Avatar of Leo Torres
Leo TorresFlag 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)
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
 
)

Open in new window

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try this:

SELECT tbl.*
FROM OPENROWSET (BULK 'C:\PythonStockPull\DataFiles\polygon\StockData_ESS_02-05-2020_02-13-12_PM.json', SINGLE_CLOB) js
CROSS APPLY OPENJSON(BulkColumn, '$.results')
WITH
(
    [ticker] varchar(20), 
    [period] varchar(20), 
    [calendarDate] Datetime, 
    [reportPeriod] Datetime, 
    [updated] Datetime, 
    [accumulatedOtherComprehensiveIncome] float, 
    [assets] float, 
    [assetsAverage] float
) tbl

Open in new window

Avatar of Leo Torres

ASKER

Thank you for the fast reply no error no NULLs just got header names with no data.

FYI.. this code returns Key, Value, type columns
all the data is 1 string in value column. Just some info if that helps
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)

Open in new window

it worked well for me.

User generated image
make sure the JSON content format is same as what you have provided above.
StockData_ESS_02-05-2020_02-13-12_PM.json
OK so the query I started with is a smaller version of the entire query. I went ahead and ran the entire actual query so it matches all the datas point in the actual file.

attached actual file

SELECT tbl.*
FROM OPENROWSET (BULK 'C:\PythonStockPull\DataFiles\polygon\StockData_ESS_02-05-2020_02-13-12_PM.json', SINGLE_CLOB) js
CROSS APPLY OPENJSON(BulkColumn, '$.results') 
WITH
(
    [ticker] varchar(20), 
    [period] varchar(20), 
    [calendarDate] Datetime, 
    [reportPeriod] Datetime, 
    [updated] Datetime, 
    [accumulatedOtherComprehensiveIncome] float, 
    [assets] float, 
    [assetsAverage] float,
    [assetsCurrent] float,
    [assetTurnover] float,
    [assetsNonCurrent] float,
    [bookValuePerShare] float,
    [capitalExpenditure] float,
    [cashAndEquivalents] float,
    [cashAndEquivalentsUSD] float,
    [costOfRevenue] float,
    [consolidatedIncome] float,
    [currentRatio] float,
    [debtToEquityRatio] float,
    [debt] float,
    [debtCurrent] float,
    [debtNonCurrent] float,
    [debtUSD] float,
    [deferredRevenue] float,
    [depreciationAmortizationAndAccretion] float,
    [deposits] float,
    [dividendYield] float,
    [dividendsPerBasicCommonShare] float,
    [earningBeforeInterestTaxes] float,
    [earningsBeforeInterestTaxesDepreciationAmortization] float,
    [EBITDAMargin] float,
    [earningsBeforeInterestTaxesDepreciationAmortizationUSD] float,
    [earningBeforeInterestTaxesUSD] float,
    [earningsBeforeTax] float,
    [earningsPerBasicShare] float,
    [earningsPerDilutedShare] float,
    [earningsPerBasicShareUSD] float,
    [shareholdersEquity] float,
    [averageEquity] float,
    [shareholdersEquityUSD] float,
    [enterpriseValue] float,
    [enterpriseValueOverEBIT] float,
    [enterpriseValueOverEBITDA] float,
    [freeCashFlow] float,
    [freeCashFlowPerShare] float,
    [foreignCurrencyUSDExchangeRate] float,
    [grossProfit] float,
    [grossMargin] float,
    [goodwillAndIntangibleAssets] float,
    [interestExpense] float,
    [investedCapital] float,
    [investedCapitalAverage] float,
    [inventory] float,
    [investments] float,
    [investmentsCurrent] float,
    [investmentsNonCurrent] float,
    [totalLiabilities] float,
    [currentLiabilities] float,
    [liabilitiesNonCurrent] float,
    [marketCapitalization] float,
    [netCashFlow] float,
    [netCashFlowBusinessAcquisitionsDisposals] float,
    [issuanceEquityShares] float,
    [issuanceDebtSecurities] float,
    [paymentDividendsOtherCashDistributions] float,
    [netCashFlowFromFinancing] float,
    [netCashFlowFromInvesting] float,
    [netCashFlowInvestmentAcquisitionsDisposals] float,
    [netCashFlowFromOperations] float,
    [effectOfExchangeRateChangesOnCash] float,
    [netIncome] float,
    [netIncomeCommonStock] float,
    [netIncomeCommonStockUSD] float,
    [netLossIncomeFromDiscontinuedOperations] float,
    [netIncomeToNonControllingInterests] float,
    [profitMargin] float,
    [operatingExpenses] float,
    [operatingIncome] float,
    [tradeAndNonTradePayables] float,
    [payoutRatio] float,
    [priceToBookValue] float,
    [priceEarnings] float,
    [priceToEarningsRatio] float,
    [propertyPlantEquipmentNet] float,
    [preferredDividendsIncomeStatementImpact] float,
    [sharePriceAdjustedClose] float,
    [priceSales] float,
    [priceToSalesRatio] float,
    [tradeAndNonTradeReceivables] float,
    [accumulatedRetainedEarningsDeficit] float,
    [revenues] float,
    [revenuesUSD] float,
    [researchAndDevelopmentExpense] float,
    [returnOnAverageAssets] float,
    [returnOnAverageEquity] float,
    [returnOnInvestedCapital] float,
    [returnOnSales] float,
    [shareBasedCompensation] float,
    [sellingGeneralAndAdministrativeExpense] float,
    [shareFactor] float,
    [shares] float,
    [weightedAverageShares] float,
    [weightedAverageSharesDiluted] float,
    [salesPerShare] float,
    [tangibleAssetValue] float,
    [taxAssets] float,
    [incomeTaxExpense] float,
    [taxLiabilities] float,
    [tangibleAssetsBookValuePerShare] float,
    [workingCapital] float
) tbl



Open in new window


Also not sure if this matters but I am running SQL Server 2016.

try this:

SELECT tbl.*
FROM OPENROWSET (BULK 'C:\PythonStockPull\DataFiles\polygon\StockData_ESS_02-05-2020_02-13-12_PM.json', SINGLE_CLOB) js
CROSS APPLY OPENJSON(BulkColumn)
WITH
(
	v NVARCHAR(MAX) '$.results' AS JSON
)
cross APPLY OPENJSON(v)
WITH
(
    [ticker] varchar(20), 
    [period] varchar(20), 
    [calendarDate] Datetime, 
    [reportPeriod] Datetime, 
    [updated] Datetime, 
    [accumulatedOtherComprehensiveIncome] float, 
    [assets] float, 
    [assetsAverage] float,
    [assetsCurrent] float,
    [assetTurnover] float,
    [assetsNonCurrent] float,
    [bookValuePerShare] float,
    [capitalExpenditure] float,
    [cashAndEquivalents] float,
    [cashAndEquivalentsUSD] float,
    [costOfRevenue] float,
    [consolidatedIncome] float,
    [currentRatio] float,
    [debtToEquityRatio] float,
    [debt] float,
    [debtCurrent] float,
    [debtNonCurrent] float,
    [debtUSD] float,
    [deferredRevenue] float,
    [depreciationAmortizationAndAccretion] float,
    [deposits] float,
    [dividendYield] float,
    [dividendsPerBasicCommonShare] float,
    [earningBeforeInterestTaxes] float,
    [earningsBeforeInterestTaxesDepreciationAmortization] float,
    [EBITDAMargin] float,
    [earningsBeforeInterestTaxesDepreciationAmortizationUSD] float,
    [earningBeforeInterestTaxesUSD] float,
    [earningsBeforeTax] float,
    [earningsPerBasicShare] float,
    [earningsPerDilutedShare] float,
    [earningsPerBasicShareUSD] float,
    [shareholdersEquity] float,
    [averageEquity] float,
    [shareholdersEquityUSD] float,
    [enterpriseValue] float,
    [enterpriseValueOverEBIT] float,
    [enterpriseValueOverEBITDA] float,
    [freeCashFlow] float,
    [freeCashFlowPerShare] float,
    [foreignCurrencyUSDExchangeRate] float,
    [grossProfit] float,
    [grossMargin] float,
    [goodwillAndIntangibleAssets] float,
    [interestExpense] float,
    [investedCapital] float,
    [investedCapitalAverage] float,
    [inventory] float,
    [investments] float,
    [investmentsCurrent] float,
    [investmentsNonCurrent] float,
    [totalLiabilities] float,
    [currentLiabilities] float,
    [liabilitiesNonCurrent] float,
    [marketCapitalization] float,
    [netCashFlow] float,
    [netCashFlowBusinessAcquisitionsDisposals] float,
    [issuanceEquityShares] float,
    [issuanceDebtSecurities] float,
    [paymentDividendsOtherCashDistributions] float,
    [netCashFlowFromFinancing] float,
    [netCashFlowFromInvesting] float,
    [netCashFlowInvestmentAcquisitionsDisposals] float,
    [netCashFlowFromOperations] float,
    [effectOfExchangeRateChangesOnCash] float,
    [netIncome] float,
    [netIncomeCommonStock] float,
    [netIncomeCommonStockUSD] float,
    [netLossIncomeFromDiscontinuedOperations] float,
    [netIncomeToNonControllingInterests] float,
    [profitMargin] float,
    [operatingExpenses] float,
    [operatingIncome] float,
    [tradeAndNonTradePayables] float,
    [payoutRatio] float,
    [priceToBookValue] float,
    [priceEarnings] float,
    [priceToEarningsRatio] float,
    [propertyPlantEquipmentNet] float,
    [preferredDividendsIncomeStatementImpact] float,
    [sharePriceAdjustedClose] float,
    [priceSales] float,
    [priceToSalesRatio] float,
    [tradeAndNonTradeReceivables] float,
    [accumulatedRetainedEarningsDeficit] float,
    [revenues] float,
    [revenuesUSD] float,
    [researchAndDevelopmentExpense] float,
    [returnOnAverageAssets] float,
    [returnOnAverageEquity] float,
    [returnOnInvestedCapital] float,
    [returnOnSales] float,
    [shareBasedCompensation] float,
    [sellingGeneralAndAdministrativeExpense] float,
    [shareFactor] float,
    [shares] float,
    [weightedAverageShares] float,
    [weightedAverageSharesDiluted] float,
    [salesPerShare] float,
    [tangibleAssetValue] float,
    [taxAssets] float,
    [incomeTaxExpense] float,
    [taxLiabilities] float,
    [tangibleAssetsBookValuePerShare] float,
    [workingCapital] float
) tbl

Open in new window


I'm kinda "new" in OPENJSON as well, not too sure if there's a better way to do that.
Thank you for your help. Is it working on your end? i am not getting an error but I just get the column names with no data. What version of SQL are you on. Could this be a version difference that is not allowing me to get results?

IN case it helps this is the link i have been using as reference
SQL Shack​​​
yea. kinda worked for me.

User generated image
I'm using

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)   Jun 15 2019 00:26:19   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 18362: )


Could this be a version difference that is not allowing me to get results?

it's unlikely since you got the fields returned, but not the data.
Thats a good point about the header information displaying but not the data.
 
this is what I am on
Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)   Jul  5 2017 23:41:17   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: )
This really sucks. I dont know where to go from here. I am not even getting a syntax error...
try simplify your json or do a basic test on openjson, see whether you can read the data?

you can test with examples at

OPENJSON (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15

but i still think the issue is with the json file content.
I will try yes. But how can it be file and it worked for you and not me? Did you make  changes to file?
btw, your file format need to be changed a bit to be a "real" json.

I forgot to tell you that.

the correct syntax would be something like this:

[{
		"status": "OK",
		"results": [{
				"ticker": "ESS",
				"period": "YA",
				"calendarDate": "2019-12-31",
				"reportPeriod": "2019-12-31",
				"updated": "2020-02-21",
				"dateKey": "2019-12-31",
				"accumulatedOtherComprehensiveIncome": -13888000,
				"assets": 12705405000,

Open in new window


while your original format is comes with quotes " and some comes with \"

["{\"status\":\"OK\",\"results\":[{\"ticker\":\"ESS\",\"period\":\"YA\",\"calendarDate\":\"2019-12-31\",\"reportPeriod\":\"2019-12-31\",\"updated\":\"2020-02-21\",\"dateKey\":\"2019-12-31

think the way the file is generated need to be updated to remove those quotes.
while your original format is comes with quotes " and some comes with \"

I guess the reason is the web service that generated the json file already return as json, but it was serialize again. that's why the json comes with extra quotes there.
is there a work around in the code. This is one file I have to import about 20,000 files changing the files manually. Not really an option. where is this "\" you speak of?
Not really an option. where is this "\" you speak of?

for example:

...  "{\"status\" ....

is there a work around in the code.

try see if you can amend the web service.

if you can't you need to parse the json content before the SQL execution.
OK I see it now
Your saying to remove the / from the file I was look at it from web browser and forgot these \. I know some powershell So I can write a script to remove these and recreate file
User generated image
Not sure what you mean by amend web service. I dont own the service I am a user. I just have an API Key an get the data.

So your theory is to remove the "\" and it will work?
You highlight the " to but I think those will need to remain.
I dont own the service I am a user. I just have an API Key an get the data.
Ok, no problem. so apparently the provider didn't provide you an actual "JSON" file : )

So your theory is to remove the "\" and it will work?

yes, that will work (of course, you need to maintain and make sure it's a valid JSON format). since JSON is just piece of text data.

for me, I just change the content in Notepad++ and validate if the content is a valid JSON format.
ok so I removed the "\". Can believe i was actually excited about getting an error.

Here is the error after removing the \
[code]
(1 row affected)
Msg 13609, Level 16, State 4, Line 23
JSON text is not properly formatted. Unexpected character 's' is found at position 4.
Completion time: 2020-05-03T00:35:33.4468068-04:00
[\code]

How can you validate json with notepad I use notepad++ as well. if it a length explanation just post a link please.


ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok got it I will just have to edit my file to look like yours before I run sql. I guess now on to powershell scripting. Thank you
question how did you convert to json then looks like there quite a few changes
question how did you convert to json then looks like there quite a few changes

you can search your favourite JSON plugin and install in Notepad++.

then just format and test when it's needed : )

for example:

User generated image