Stevie Zakhour
asked on
Import Json file into MS SQL
Hi Guys
Trying to import a json formatted file into sql, the query is below
The json formatted file is below
When I run the query I get no errors however there is no data. See attached png file called Snip20200203_3.Png, this will show you the result after running the query in MS SQL 2016
Any help is greatly appreciated.
Snip20200203_3.png
Trying to import a json formatted file into sql, the query is below
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
)
The json formatted file is below
{
"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"
}
}
When I run the query I get no errors however there is no data. See attached png file called Snip20200203_3.Png, this will show you the result after running the query in MS SQL 2016
Any help is greatly appreciated.
Snip20200203_3.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Ryan - do you know if there is a way to bulk upload any .json file, i.e.
select @JSON = BulkColumn from openrowset (bulk 'C:\Users\szakhour\Desktop \Freak\*.j son', single_clob) as j
select @JSON = BulkColumn from openrowset (bulk 'C:\Users\szakhour\Desktop
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.
ASKER
Thanks Ryan, I don't think that would work for a batch import. For example, I have a folder in directory C:\Users\szakhour\Desktop\ Freak.
In this Freak folder, there is 2 .json files, see below
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
In this Freak folder, there is 2 .json files, see below
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
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?
ASKER
Thank Ryan, I’ll raise another question then. Thank you for your help
you can simply try this:
Open in new window