Loading line-delimited JSON files in SQL Server 2016

  • Article

Loading Line-Delimited JSON files in SQL Server

One of the problem with JSON is the fact that you cannot continuously append JSON messages. If you want to have valid array of JSON objects you will need to surround them with brackets. Once you add final bracket, you cannot add new data.

Line delimited JSON (aka. JSON lines or Newline-delimited JSON) is an alternative JSON format that might be a good choice for continuous serialization of streaming JSON objects.  LD JSON address one of the main issues in standard JSON format – ability to continuously append valid JSON objects. LD-JSON introduces few changes in standard JSON format:

  1. New line is separator between objects
  2. Stream of JSON objects is not surrounded with brackets so you can continuously append JSON information in the file.

An example of LD-JSON content is shown in the following example:

 {"time":"2015-11-27T02:33:05.063","ip":"214.0.57.12","request":"/", "method":"GET", "status":"200"}
{"time":"2015-11-27T02:33:06.003","ip":"224.12.07.25","request":"/","method":"GET", "status":"200"}
{"time":"2015-11-27T02:33:06.032","ip":"192.10.81.115","request":"/contact", "method":"POST", "status":"500", "exception":"Object reference not set to an instance of object",”stackTrace”:”…” }
……..
{"time":"2015-11-27T02:37:06.203","ip":"204.12.27.21","request":"/login","method":"GET", "status":"200"}
{"time":"2015-11-27T02:37:12.016","ip":"214.0.57.12","request":"/login", "method":"POST","status":"404", "exception":"Potentially dangerous value was detected in request"}

Now we can read this file with FORMATFILE and run the same report:

 INSERT INTO MyTable(time, request, method, status) 
SELECT time, request, method, status
 FROM OPENROWSET(BULK 'C:\logs\log-ld-json-2015-11-27.txt', FORMATFILE= 'c:\logs\csv.fmt') AS log_file
      CROSS APPLY OPENJSON(json) WITH( time datetime, status int, method varchar(10), request nvarchar(200)) AS log

You can read these values and load them in standard SQL tables.
You would need to have format file with the following content:

 13.0
1
1 SQLCHAR 0 0 "\r\n" 1 json ""

Or you ca use equivalent XML format file:

 <?xml version="1.0"?>
<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8000" COLLATION="Serbian_Latin_100_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="json" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Note that "json" column in the T-SQL query above is defined in format file.
You can explicitly specify size in format file or change delimiter to \n, e.g.:

 13.0
1
1 SQLCHAR 0 80000 "\n" 1 json ""