次の方法で共有

Exporting tables from SQL Server in json line-delimited format using BCP.exe

  • [アーティクル]

Line-delimited JSON is one common format used to exchange data between systems and for streaming JSON data. SQL Server can be used to export content of tables into line-delimited JSON format.

Line-delimited JSON is a variation of JSON format where all JSON objects are stored in single line delimited with new-line characters, e.g.:

 {"ProductID":15,"Name":"Adjustable Race","Price":75.9900,"Quantity":50}
{"ProductID":16,"Name":"Bearing Ball","Color":"Magenta","Size":"62","Price":15.9900,"Quantity":90}
{"ProductID":17,"Name":"BB","Color":"Magenta","Size":"62","Price":28.9900,"Quantity":80}
{"ProductID":18,"Name":"Blade","Color":"Magenta","Size":"62","Price":18.0000,"Quantity":45}

Although this is not a valid JSON format, many system use it to exchange data.

One advantage of line-delimited JSON format compared to the standard JSON is the fact that you can append new JSON objects at the end of the file without removing closing array bracket as in the standard JSON.

In this post I will show you how to export the content of a table shown in the following listing in line-delimited JSON format:

 CREATE TABLE Product (
 ProductID int IDENTITY PRIMARY KEY,
 Name nvarchar(50) NOT NULL,
 Color nvarchar(15) NULL,
 Size nvarchar(5) NULL,
 Price money NOT NULL,
 Quantity int NULL
)

If you want to select all rows from the table in JSON format, you can use standard FOR JSON clause:

 select ProductID, Name, Color, Size, Price, Quantity
from Product for json path

This query will return all rows as JSON objects separated with comma and wrapped with [ and ].

Small modification of query will enable you to return one object per row:

 select (select ProductID, Name, Color, Size, Price, Quantity for json path, without_array_wrapper)
from Product

You can use standard bcp.exe tool to generate line delimited JSON files using this query:

 bcp "select (select ProductID, Name, Color, Size, Price, Quantity for json path, without_array_wrapper) from Product" queryout .\products.json  -c -S ".\SQLEXPRESS" -d ProductCatalog -T

Note that I'm using queryout option because I have specified the T-SQL query that will extract data, and -c option that will generate the output in character format. This option does not prompt for each field; it uses char as the storage type, without prefixes and \r\n (newline character) as the row terminator.

Running this bcp command would generate line-delimited JSON file containing one JSON object for every row in the table.

Comments

  • Anonymous
    March 23, 2018
    row count in output very limited. I receive only 840 rows. At the end json truncated.