CODEWITHSUNDEEP
sql-serverjsonparsing
CarCrazyBen
CarCrazyBen

Reputation: 1136

how to process multiple JSON documents

Using SQL Server 2017 I figured out how to parse a single JSON file containing a single JSON document for storing in a table.

However, I need to be able to process a file containing many JSON documents. Are there built-in functions to do this? Or can anyone explain how this can be done?

Upvotes: 0

Views: 1498

Answers (2)

Chandra Sekhar Reddy P
Chandra Sekhar Reddy P

Reputation: 1

I have tried the above script to load multipe json files which are in a folder. It's not working as expected then i did some ground work on this and achived that task using dynamic sql.

Suppose you have 3 files in a folder and you want to load those json files into sql server follow this script. This works well.

DECLARE @i INT = 1 Declare @SQL Varchar(200) DECLARE @file Varchar(100)

WHILE(@i < 4) BEGIN SET @file = '''E:\DrugBank\json1' + cast(@i AS VARCHAR(5)) + '.json'''; --Select @file SET @SQL= 'INSERT INTO json_table SELECT * FROM OPENROWSET(BULK '+@file+', SINGLE_CLOB ) AS e'

   PRINT @SQL
   EXEC (@SQL)
   SET @i = @i + 1 ;

END

Upvotes: 0

CarCrazyBen
CarCrazyBen

Reputation: 1136

Repeating myself somewhat, but I think I have this figured out.

  1. I created a single file of 500 indivdual JSON documents
  2. I manually added the JSON-specific opening and closing bracket racket as the 1st and last lines.

  3. I used this syntax to query the file:

    select SQ1.* from OPENROWSET (BULK 'C:\Users\Username\Documents\SQL Server Management Studio\Projects\json_files\dev_AllJsonDownload.json', SINGLE_CLOB) as mytest CROSS APPLY OPENJSON(BulkColumn) WITH (...colA ...colB ...colC ...colN ) as SQ1

This returned what I wanted.

Upvotes: 1

Related Questions