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
JSONSQL* import

Avatar of undefined
Last Comment
Stevie Zakhour

8/22/2022 - Mon
Ryan Chong

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
Ryan Chong

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Ryan Chong

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.

Your help has saved me hundreds of hours of internet surfing.
fblack61
Stevie Zakhour

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

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 Chong

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

ASKER
Thank Ryan, I’ll raise another question then. Thank you for your help
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.