SQL/Azure or other database table to JSON using jq

With SQL server it is easy enough to use FOR Json to convert a table to JSON but if using a database that doesn't have a native conversion, we can do it using jq. Actually this is creating JSON from a CSV file so it can convert from any database including conventional DBs like Oracle or cloud DBs like Snowflake or Redshift.

jq is primarily a Linux tool, but there is a Windows and a Mac version that makes it a universal process. It is useful to get familiar with jq if you need to write a script to manipulate JSON too, for example I have used it to move data from tables to Kafka topics.

It is worth noting that jq installation doesn't add it to the path, and the executable is called jq-win64 (or 32). I usually create a symbolic link to a jq.exe in a tools folder and add that to the path. It is easy to create links for all the tools you need in a single folder, to not go through adding each to the path.

Using sqlcmd.exe for SQL server, command line is relatively simple. 

1. get jq to read the full input and treat it as a generic string using -slurp and --raw-input options
2. split it on line breaks, that will create one record for each raw
3. split on the separator (I used ~ since data has comma) and assign key names

jq can pipe data within the application, so all this can be done in the same command. But unlike in linux, double quotes has to be escaped. When put all together,

sqlcmd -S . -U user -P PW -d northwind -s~ -y 0  -Q "select * from orders" | jq -c --slurp --raw-input --raw-output  "split(\"\n\") |  map(split(\"~\")) |map({\"OrderID\":.[0], \"CustomerID\":.[1], \"EmployeeID\":.[2], \"OrderDate\":.[3], \"RequiredDate\":.[4], \"ShippedDate\":.[5], \"ShipVia\":.[6], \"Freight\":.[7], \"ShipName\":.[8], \"ShipAddress\":.[9],\"ShipCity\":.[10],\"ShipRegion\":.[11],\"ShipPostalCode\":.[12], \"ShipCountry\":.[13]})"  > test.txt

Format using JSTools in Notepad++,



All the records are there with a few blank records at the end, that is due to the message from SQL server at the end. If needs to take it out, awk would do it easily like in my last post.