Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'C:\Users\szakhour\Desktop\LastScore.json', SINGLE_CLOB) import
SELECT *
FROM OPENJSON (@JSON)
WITH
(
LeaderboardName varchar(20),
LeaderboardNameFull varchar(20),
PlayerCount varchar(20),
PlayerName varchar(20),
Score nvarchar(20),
ScoreDetails varchar(10)
)
CROSS APPLY OPENJSON (ScoreDetails)
WITH(
ZombiesKilled varchar(15)'$.ZombiesKilled',
Accuracy varchar(50)'$.Accuracy',
DamageTaken varchar(20)'$.DamageTaken',
SecondsInLevel varchar(30)'$.SecondsInLevel',
Deaths varchar(50)'$.Deaths' --AS JSON
)
{
"LeaderboardName": "Missile base",
"LeaderboardNameFull": "Missile base four players",
"PlayerCount": 4,
"PlayerName": "PLAYER",
"Score": 35360,
"ScoreDetails": {
"ZombiesKilled": "335",
"Accuracy": "31.32",
"DamageTaken": "100",
"SecondsInLevel": "1273",
"Deaths": "1"
}
}
select @JSON = BulkColumn from openrowset (bulk 'C:\Users\szakhour\Desktop\Freak\*.json', single_clob) as j
That should worked as well.
Example below worked for me:
Declare @JSON varchar(max)
select @JSON = BulkColumn from openrowset (bulk 'D:\Ryan\temp\test.json', single_clob) as j
SELECT *
FROM OPENJSON (@JSON)
WITH
(
LeaderboardName varchar(20),
LeaderboardNameFull varchar(20),
PlayerCount varchar(20),
PlayerName varchar(20),
Score nvarchar(20),
ZombiesKilled varchar(15)'$.ScoreDetails.ZombiesKilled',
Accuracy varchar(50)'$.ScoreDetails.Accuracy',
DamageTaken varchar(20)'$.ScoreDetails.DamageTaken',
SecondsInLevel varchar(30)'$.ScoreDetails.SecondsInLevel',
Deaths varchar(50)'$.ScoreDetails.Deaths'
)
Where test.json got the exact content as what you have shared.
What I want to do is import whatever .json file comes into the Freak folder into SQL whenever the script is run. I hope this makes sense. If not let me know
I see... if that's the case, probably we need to do some looping and load the data files accordingly.
some possible approaches are SSIS, self-customized ETL programs, etc.
If you want to discuss this further in your next question?
you can simply try this:
Open in new window