Query Json file
I wan to query the Ticker, Status,queryCount, adjusted columns but I can't seem to be able to query the values inside result with the values outside that variable.
this is my json file below
SELECT tbl.*
FROM OPENROWSET (BULK 'C:\SomeFile.json', SINGLE_CLOB) js
CROSS APPLY OPENJSON(BulkColumn)
WITH
(
v NVARCHAR(MAX) '$.results' AS JSON
)
cross APPLY OPENJSON(v)
WITH
(
[v] int,
[vw] decimal(15,5),
[o] decimal(15,5),
[c] decimal(15,5),
[h] decimal(15,5),
[l] decimal(15,5),
[t] bigint,
[n] smallint
) tblthis is my json file below
[{
"ticker": "VIG",
"status": "OK",
"queryCount": 1571,
"resultsCount": 1571,
"adjusted": false,
"results": [{
"v": 117,
"vw": 112.0438,
"o": 112,
"c": 112,
"h": 112,
"l": 112,
"t": 1588330800000,
"n": 5
}
1. Your json example is not well formed. But it seems correct as Ryan Chong mentioned.
2. You can simplify your query by direct query to the desired node:
2. You can simplify your query by direct query to the desired node:
declare @json nvarchar(max) = '
[{
"ticker": "VIG",
"status": "OK",
"queryCount": 1571,
"resultsCount": 1571,
"adjusted": false,
"results": [{
"v": 117,
"vw": 112.0438,
"o": 112,
"c": 112,
"h": 112,
"l": 112,
"t": 1588330800000,
"n": 5
}, {
"v": 2117,
"vw": 2112.0438,
"o": 2112,
"c": 2112,
"h": 2112,
"l": 2112,
"t": 21588330800000,
"n": 25
}
]
}
]
'
SELECT *
FROM OPENJSON(@json, '$[0].results')
WITH (
[v] int,
[vw] decimal(15,5),
[o] decimal(15,5),
[c] decimal(15,5),
[h] decimal(15,5),
[l] decimal(15,5),
[t] bigint,
[n] smallint
) tbl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So i thought this was the end of if but I can not put this select in a stored procedure?
Error
Msg 102, Level 15, State 1, Procedure InsertAggregateData, Line 10 [Batch Start Line 0]
Incorrect syntax near '@jSONPath'.
does not like the parameter as a variable.. Any ideas here?
Error
Msg 102, Level 15, State 1, Procedure InsertAggregateData, Line 10 [Batch Start Line 0]
Incorrect syntax near '@jSONPath'.
does not like the parameter as a variable.. Any ideas here?
Create procedure dbo.InsertAggregateData(
@jSONPath nvarchar(255)
)
as
DECLARE @json NVARCHAR(MAX)-- = N'
SELECT @json = BulkColumn
FROM
OPENROWSET(BULK @jSONPath, SINGLE_CLOB) js;
insert into dbo.Aggregationdata([ticker],[status],[queryCount],[resultsCount],[adjusted],[resultVW],[resultO],[resultC],[resultH],[resultL],[resultT],[resultN])
SELECT ParsedJson.ticker,
ParsedJson.status,
ParsedJson.queryCount,
ParsedJson.resultsCount,
ParsedJson.adjusted,
ParsedJsonResults.resultVW,
ParsedJsonResults.resultO,
ParsedJsonResults.resultC,
ParsedJsonResults.resultH,
ParsedJsonResults.resultL,
ParsedJsonResults.resultT,
ParsedJsonResults.resultN --into #tmp
FROM
OPENJSON(@json)
WITH
(
ticker NVARCHAR(255) '$.ticker',
[status] NVARCHAR(255) '$.status',
queryCount INT '$.queryCount',
resultsCount INT '$.resultsCount',
adjusted NVARCHAR(255) '$.adjusted',
results NVARCHAR(MAX) '$.results' AS JSON
) ParsedJson
OUTER APPLY
OPENJSON(results)
WITH
(
resultVW DECIMAL(15, 5) '$.vw',
resultO DECIMAL(15, 5) '$.o',
resultC DECIMAL(15, 5) '$.c',
resultH DECIMAL(15, 5) '$.h',
resultL DECIMAL(15, 5) '$.l',
resultT BIGINT '$.t',
resultN SMALLINT '$.n'
) ParsedJsonResults;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow what a pain in the as$. Ok will do thank you!
The BULK operations require a literal for the file name. You cannot use a variable. As Ryan wrote, you need dynamic SQL. E.g.
CREATE PROCEDURE dbo.InsertAggregateData (
@JsonPath NVARCHAR(255)
)
AS
SET NOCOUNT ON;
DECLARE @Statement NVARCHAR(MAX) = 'INSERT INTO #Json ( Json ) SELECT BulkColumn FROM OPENROWSET(BULK ''@JsonPath'', SINGLE_CLOB);'
CREATE TABLE #Json ( Json NVARCHAR(MAX) NOT NULL );
SET @Statement = REPLACE(@Statement, '@JsonPath', REPLACE(@JsonPath, '''', ''''''));
EXECUTE sp_ExecuteSql @Statement
-- use #Json
test data:
Open in new window
What you try to getting here?