Generalized way to extract JSON from a relational database?

Here is a useful set of functions for converting relational data to JSON and XML and from JSON back to tables: https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

SQL Server 2016 is finally catching up and adding support for JSON.

The JSON support still does not match other products such as PostgreSQL, e.g. no JSON-specific data type is included. However, several useful T-SQL language elements were added that make working with JSON a breeze.

E.g. in the following Transact-SQL code a text variable containing a JSON string is defined:

DECLARE @json NVARCHAR(4000)SET @json = N'{    "info":{        "type":1,      "address":{          "town":"Bristol",        "county":"Avon",        "country":"England"      },      "tags":["Sport", "Water polo"]   },   "type":"Basic"}'

and then, you can extract values and objects from JSON text using the JSON_VALUE and JSON_QUERY functions:

SELECT  JSON_VALUE(@json, '$.type') as type,  JSON_VALUE(@json, '$.info.address.town') as town,  JSON_QUERY(@json, '$.info.tags') as tags

Furhtermore, the OPENJSON function allows to return elements from referenced JSON array:

SELECT valueFROM OPENJSON(@json, '$.info.tags')

Last but not least, there is a FOR JSON clause that can format a SQL result set as JSON text:

SELECT object_id, nameFROM sys.tablesFOR JSON PATH

Some references:

• https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server
• https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server
• https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-1-of-4/
• https://www.red-gate.com/simple-talk/sql/learn-sql-server/json-support-in-sql-server-2016/

I think one 'generalized' solution will be as follows:-

1. Create a 'select' query which will join all the required tables to fetch results in a 2 dimentional array (like CSV / temporary table, etc)
2. If each row of this join is unique, and the MongoDB schema and the columns have one to one mapping, then its all about importing this CSV/Table using MongoImport command with required parameters.
3. But a case like above, where a given Customer ID can have an array of 'orders', needs some computation before mongoImport.
   You will have to write a program which can 'vertical merge' the orders for a given customer ID.For small set of data, a simple java program will work. But for larger sets, parallel programming using spark can do this job.