Link to home
Start Free TrialLog in
Avatar of Stevie Zakhour
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

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
) 

Open in new window


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"
  }
}

Open in new window


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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

you can simply try this:


SELECT a.*, b.*
FROM OPENJSON (@JSON)
WITH 
(
    LeaderboardName varchar(20), 
    LeaderboardNameFull varchar(20), 
    PlayerCount varchar(20), 
    PlayerName varchar(20), 
    Score nvarchar(20)
) a
CROSS APPLY OPENJSON (@JSON) 
WITH(
        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'
) b

Open in new window

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
Avatar of Stevie Zakhour
Stevie Zakhour

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\*.json', single_clob) as j

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'
)

Open in new window

Where test.json got the exact content as what you have shared.

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

User generated image
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?

Thank Ryan, I’ll raise another question then. Thank you for your help