Opening ndJSON format in SQL Server 2016

Question

How do I open ndJSON format in SQL Server 2016? I am able to open with JSON format, but am clueless on how to do it with ndJSON.

Is there a particular function in SQL Server that will do this, or is there another approach?

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:\examplepath\filename.JSON', SINGLE_CLOB) as j


Select * FROM OPENJSON(@JSON)
With (House varchar(50), 
      Car varchar(4000) '$.Attributes.Car',
      Door varchar(4000) '$.Attributes.Door',
      Bathroom varchar(4000) '$.Attributes.Bathroom' ,
      Basement varchar(4000) '$.Attributes.Basement' ,
      Attic varchar(4000) '$.Attributes.Attic'
      ) as Dataset
Go

JSON Format:

[

{"House":"Blue","Attributes":{"Car":"Camry","Door":"Small","Bathroom":"Medium","Basement":"Dark","Attic":"1"}},
{"House":"Red","Attributes":{"Car":"Thunderbird","Door":"Large","Bathroom":"Small","Basement":"Light","Attic":"4"}}

]

ndJSON Format:

{"House":"Blue","Attributes":{"Car":"Camry","Door":"Small","Bathroom":"Medium","Basement":"Dark","Attic":"1"}}
{"House":"Red","Attributes":{"Car":"Thunderbird","Door":"Large","Bathroom":"Small","Basement":"Light","Attic":"4"}}

There's no `ndjson` format. That's the name of a domain squatter that tried to capture the name and essentially copied everything from the JSON standard. — Panagiotis Kanavos, May 16 '18 at 11:55
check Wikipedia's entry on [JSON Streaming](https://en.wikipedia.org/wiki/JSON_streaming) to understand what those records are and why they are used. In short, it's not one JSON string. It's multiple individual JSON records sent/stored in a stream — Panagiotis Kanavos, May 16 '18 at 11:56

score 5 · Accepted Answer · answered May 16 '18 at 10:56

5

You can use FORMATFILE clause:

SELECT  House, Car, Door, Bathroom, Basement, Attic   
FROM OPENROWSET (BULK 'D:\ndjson\ndjson.json', FORMATFILE= 'D:\ndjson\csv.fmt' ) as j
  CROSS APPLY OPENJSON(json) With 
  (   House    varchar(50), 
      Car      varchar(4000) '$.Attributes.Car',
      Door     varchar(4000) '$.Attributes.Door',
      Bathroom varchar(4000) '$.Attributes.Bathroom' ,
      Basement varchar(4000) '$.Attributes.Basement' ,
      Attic    varchar(4000) '$.Attributes.Attic'
  ) as Dataset

Where the file csv.fmt contains:

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

Results:

More info on this MSDN blog

answered May 16 '18 at 10:56

Andrea

11,801
17
65
72

Unfortunately @Andrea , I get this error: JSON path is not properly formatted. Unexpected character 'A' is found at position 1. – Dip May 16 '18 at 15:28
Oh wow, nevermind. My issue was a typo, I had '$Attributes.Attic', instead it should have been '$.Attributes.Attic' , it was missing the period after the dollar sign. Thanks! – Dip May 16 '18 at 15:50

Opening ndJSON format in SQL Server 2016

1 Answers1