Geek Answers Handbook

A generic way to retrieve JSON from a relational database?

Well, maybe this is too big for StackOverflow, but is there a good generalized way to collect data in relational tables into hierarchical JSON?

For example, let's say we have a “customers” table and an “orders” table. I want the result 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 would prefer not to write a lot of procedural code in order to iterate over the main table and select orders several times and apply them. It will be very slow.

I am using MS SQL Server database, but I will need to do the same with MySQL soon. I use Java and JDBC for access. If one of these databases had some kind of magical way to build these records on the server side, that would be ideal.

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

+7
source share
3 answers

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

+1
source

I think one “generalized” solution would be the following: -

  • Create a 'select' query that will combine all the necessary tables to get the results in 2 arrays (e.g. CSV / temporary table, etc.)
  • If each row of this join is unique, and the MongoDB schema and columns are one to one, then all this concerns the import of this CSV / table using the MongoImport command with the required parameters.
  • But such a case, as indicated above, when a given customer identifier can have an array of "orders", some calculation is required before mongoImport.
    You will need to write a program that can "vertically merge" orders for a given customer identifier. For a small data set, a simple java program will work. But for large sets, concurrent spark programming can do the job.
0
source

There is no generic way because SQL Server does not support JSON as its data type. To do this, you need to create your own "generalized method".

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

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

-one
source

More articles:


All Articles