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?

Answer 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/