D:Temp>bcp "select (select * from lineitem for json path)" queryout lineitems.json -d tpch -S .SQLEXPRESS -T -w Starting copy... 1 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 103438 Average : (0.01 rows per sec.)
The query will format all rows from lineitem table, format them as JSON text and return them as a single cell. I’m using Unicode format (-w flag). As a result, bcp.exe will generate 4.35 GB (4,677,494,824 bytes) file containing one big JSON array.
Now I will load the content of this file using OPENROWSET(BULK) and pass content to OPENJSON function that will parse it, take the values from l_discount key, and find the average value:
select avg([l_discount]) from openrowset(bulk 'D:Templineitems.json', SINGLE_NCLOB) f cross apply openjson(f.BulkColumn) with([l_discount] [money])
In my SQL Server 2016 Express edition this query is finished in 1min 53 sec.
Functions that can parse JSON in SQL Server 2016 do not have any constraint regarding the size of JSON document. As you might see in this example, I can successfully parse 4GB JSON document, which is 2x bigger than maximum size of NVARCHAR(MAX) that can be stored in tables.