Generalized way to extract JSON from a relational database?

Question

Ok, maybe this is too broad for StackOverflow, but is there a good, generalized way to assemble data in relational tables into hierarchical JSON?

For example, let's say we have a "customers" table and an "orders" table. I want the output to look like this:

{
    "customers": [
        {
            "customerId": 123,
            "name": "Bob",
            "orders": [
                {
                    "orderId": 456,
                    "product": "chair",
                    "price": 100
                },
                {
                    "orderId": 789,
                    "product": "desk",
                    "price": 200
                }
            ]
        },
        {
            "customerId": 999,
            "name": "Fred",
            "orders": []
        }
    ]
}

I'd rather not have to write a lot of procedural code to loop through the main table and fetch orders a few at a time and attach them. It'll be painfully slow.

The database I'm using is MS SQL Server, but I'll need to do the same thing with MySQL soon. I'm using Java and JDBC for access. If either of these databases had some magic way of assembling these records server-side it would be ideal.

How do people migrate from relational databases to JSON databases like MongoDB?

Haha. Hah. Haaahaha .. good one :D SQL Server can do such queries *for XML output* (as a vendor feature), but there is *no standard provision for this task in SQL*. The use of an appropriate ORM/mapper can make such *client* (perhaps in a web-service?) transformations easier. (I find the process is "relatively painless" in C#/LINQ, but there are likely tools designed specifically for this mapping.) — user2246674, May 06 '13 at 20:14
you mean a document collection, 1 document per customer with arrays for their orders, right ? — Drew, May 06 '13 at 20:38

score 1 · Answer 1 · answered Oct 26 '13 at 00:39

1

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/

answered Oct 26 '13 at 00:39

rainabba

3,804
35
35

I've seen this and used this. It isn't perfect, but it worked pretty well for me. – Don Rolling Mar 10 '16 at 19:00

score 1 · Answer 2 · answered Mar 28 '18 at 22:51

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 value
FROM 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, name
FROM sys.tables
FOR JSON PATH

Some references:

Vibha · Answer 3 · 2016-04-25T06:40:07.673

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

Create a 'select' query which will join all the required tables to fetch results in a 2 dimentional array (like CSV / temporary table, etc)
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.
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.

score 0 · Answer 4 · answered Mar 28 '18 at 21:44

0

SQL Server 2016 now supports reading JSON in much the same way as it has supported XML for many years. Using OPENJSON to query directly and JSON datatype to store.

answered Mar 28 '18 at 21:44

Steve

1

score -1 · Answer 5 · answered May 07 '13 at 09:27

-1

There is no generalized way because SQL Server doesn’t support JSON as its datatype. You’ll have to create your own “generalized way” for this.

Check out this article. There are good examples there on how to manipulate sql server data to JSON format.

https://www.simple-talk.com/blogs/2013/03/26/sql-server-json-to-table-and-table-to-json/

answered May 07 '13 at 09:27

George Wesley

117
1
3

Here is a nice way to manage JSON (and XML and relational) data in SQL Server in a generalized manner: https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/ – rainabba Oct 26 '13 at 00:39

Generalized way to extract JSON from a relational database?

5 Answers5