We help IT Professionals succeed at work.

Import Json file into MS SQL

Stevie Zakhour
Stevie Zakhour asked
on
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
Comment
Watch Question

View Solution Only

Ryan ChongSoftware Team Lead

Commented:

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
Software Team Lead
Commented:

OR actually can simplified to:


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

Author

Commented:
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
Ryan ChongSoftware Team Lead

Commented:

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.

Stevie Zakhour

Author

Commented:
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

Snip20200203_5.png
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
Ryan ChongSoftware Team Lead

Commented:

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?

Stevie Zakhour

Author

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