May 20, 2021 at 9:06 pm
The issue isn't so much that SQL server can't parse and format json it's that the data is represented in fundamentally different ways.
In which case why bother with JSON? Isn't representing the data in two different ways creating redundancy and therefore potential inconsistencies?
Wasn't overcoming the limitations of hierarchical representations like JSON, XML or in the old days IBM's hierarchical DBMS IMS one of the most important reasons that the relational model exists?
Because they're different tools for different jobs. JSON or XML are supposed to be versatile(unlike older mainframe type hierarchies) and capable of being transmitted and consumed by a broad range of systems. Where as relational databases are extremely rigid in how they store data and how they can be accessed.
And yes that means that data will be represented in different ways, hence the challenges of mapping between them.
May 21, 2021 at 5:20 am
JSON or XML are supposed to be versatile(unlike older mainframe type hierarchies) and capable of being transmitted and consumed by a broad range of systems. Where as relational databases are extremely rigid in how they store data and how they can be accessed.
On the contrary, the relational representation is far more flexible than a hierarchical one. What data is sent to the client is the product of a query - and hence totally flexible. Furthermore the data can be represented relationally without having to come from a database - so you have complete flexibility when transfering data - you send the metadata and the data. Once you have the data represented as relations you have the full capabilities of the relational operations at your disposal. This is surely much easier to work with than parsing JSON?
May 21, 2021 at 1:22 pm
Because they're different tools for different jobs. JSON or XML are supposed to be versatile(unlike older mainframe type hierarchies) and capable of being transmitted and consumed by a broad range of systems. Where as relational databases are extremely rigid in how they store data and how they can be accessed.
And yes that means that data will be represented in different ways, hence the challenges of mapping between them.
When XML and JSON extensions were added to t-sql why didn't SQL Server also inherit the good properties of those data formats? It did, right? But ORM is essentially an obstruction to that.
I'm curious if there's a typical use case where "the challenges of mapping" are essential to coding some design specification. What's the simplest example you could point to?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 21, 2021 at 2:38 pm
On the contrary, the relational representation is far more flexible than a hierarchical one. What data is sent to the client is the product of a query - and hence totally flexible. Furthermore the data can be represented relationally without having to come from a database - so you have complete flexibility when transfering data - you send the metadata and the data. Once you have the data represented as relations you have the full capabilities of the relational operations at your disposal. This is surely much easier to work with than parsing JSON?
That entirely depends on what you are trying to do with the data. It's easy to say just send the meta data and the data and then it's good to go, but how do you actually do that? There is no standard for what is relational meta data or how the data itself should be sent or for that matter any requirement that the data have relationships defined in a standard way. JSON is just JSON, any system that parses JSON can parse JSON, be that SQL Server, powershell or any other tool you might be using. Plus because the relationships are implicit to the data not defined by meta data it's very easy to parse through them without having to recreate them. JSON is also much more resilient to changes to the structure than relational data is, for example if I have an API that is used by thousands of different clients with JSON I can add fields or entirely new relationships and that shouldn't be a problem, whereas that might blow up a relational model.
This isn't a case of one is better than the other, they are simply different tools for different jobs. Relational databases are great for storing large amounts of well structured data that you want to be able to efficiently slice up and query into small chunks. JSON is great for task oriented data transfers that are going to be used in their entirety and potentially across multiple different platforms.
May 21, 2021 at 3:01 pm
JSON is just JSON, any system that parses JSON can parse JSON, be that SQL Server, powershell or any other tool you might be using.
JSON has the fundamental problem of being hierarchical.
Consider the following
Customer
Order
Order Line
Product
Product Group
Product
How do you write a query that shows which customers bought particular product groups? You have to navigate down the customer hierarchy and then back up the product group one. In the relational model no navigation is necessary. This is one of the reasons that relational DBMS swept aside the hierarchical approaches of the 60s and 70s.
There is no standard for what is relational meta data or how the data itself should be sent or for that matter any requirement that the data have relationships defined in a standard way.
There is no syntactic standard, that's true, though all you need is a standard interface that implements the relational operators (restriction, projection, union, intersect and minus), you are then free to send the data physically however you want.
A short while ago XML was all the trend, now it's JSON. How long will JSON last before someone comes up with something new? The relational model has existed for more than 50 years and shows absolutely no sign of going away.
Relational databases are great for storing large amounts of well structured data that you want to be able to efficiently slice up and query into small chunks.
It is perfectly possible for data to be represented relationally without it being physically stored. You can store the data physically however you want, the relational model is solely concerned about the logical level and has nothing to say about storage.
May 21, 2021 at 3:05 pm
Tabs aren't recognized, here are the hierarchies again:
Customer
----Order
--------Order Line
-----------Product
Product Group
-----Product
May 21, 2021 at 4:03 pm
How do you write a query that shows which customers bought particular product groups? You have to navigate down the customer hierarchy and then back up the product group one.
What if that's not what the data is needed for? For example I'm sending orders to a fulfillment system, it doesn't need to be filtered down because that has already been done, it just needs to process through the data it's been given. Which actually becomes harder to do if you have to load meta data, load a bunch of relational tables and then join them back together.
There is no syntactic standard, that's true, though all you need is a standard interface that implements the relational operators (restriction, projection, union, intersect and minus), you are then free to send the data physically however you want.
Right but there is no standard interface that does that right now. I can say this data will be in JSON format and that is a common format that can be processed by any number of standard interfaces. There is no equivalent for I'll be sending you relational data.
If someone said to me we'll be sending you data in JSON format I'd have a very clear idea of what I'm getting, on the other hand if a vendor said we'll be sending you relational data I'd be terrified.
Different tools for different jobs.
May 21, 2021 at 4:18 pm
So once again I can say this data will be in JSON format and that is a common format that can be processed by any number of standard interfaces.
The proliferation of JSON parsers does tend to indicate that is everything other than easy to read or manipulate. Why "any number" why not just one?
May 21, 2021 at 5:05 pm
So once again I can say this data will be in JSON format and that is a common format that can be processed by any number of standard interfaces.
The proliferation of JSON parsers does tend to indicate that is everything other than easy to read or manipulate. Why "any number" why not just one?
Because not everyone uses the same tools? Which was exactly my point JSON is a standard that is commonly accepted by a large number of tools. There is no equivalent for a relational data set.
May 21, 2021 at 5:12 pm
There is no equivalent for a relational data set.
You have perhaps heard of SQL?
May 21, 2021 at 5:37 pm
I like no/low code tools, like Power BI and others.
Back on topic!
I am very impressed with what Microsoft have done with Power Query. They've put a lot of thought into how people actually work with analysing data. That you can move forward and back through the steps you made in transforming the data is a very useful feature.
May 21, 2021 at 5:39 pm
SQL is not a data format, it's s syntax for querying data.
You have perhaps heard of the DDL part of SQL?
May 21, 2021 at 5:53 pm
SQL is not a data format, it's s syntax for querying data.
You have perhaps heard of the DDL part of SQL?
Which is once again not a data format, it's syntax telling the DB engine how to create the actual data structure.
It is also highly platform specific, unlike JSON.
May 21, 2021 at 5:57 pm
The information_schema is an ansi standard for SQL metadata supported by every DBMS. So the format for representing SQL data is standard.
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply