I did a bit of digging because I was curious to the answer for this question. My understanding is that the closest thing MariaDB has currently is dynamic columns[0]. Essentially they allow you to have arbitrary key-value pairs associated with each row in a table. According to a talk from a couple years ago[1], it seems like the plan is to use this as a stepping stone toward JSON support. Note that MariaDB also supports virtual columns[2] which could be used to create indexes over dynamic columns.
How does pg handle this? I believe it has GIN index support on JSON columns but that can not do range queries. Is it possible to use a functional index in postgresql on JSON types too?
I just skimmed the documentation[1]. Doesn't look look like it:
8.14.4. jsonb Indexing
...
However, the [GIN] index could not be used for queries like the following:
-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';
To do a range query you would need to use a BTree index (or a GiST index that is essentially configured to simulate a BTree) over the extracted value, not on the column itself, value (exactly as is possible now in MySQL as of two months ago, as they finally caught up and got a way to do functional indexes, specifically designed for this use case). PostgreSQL has been capable of this essentially forever (if you really needed to do this ten years ago, one line of plpython to parse the JSON and extract the field would have worked perfectly and built a perfectly efficient index; just no one wanted to store this kind of stuff until MongoDB tried to push it as the future, and so no one cared to bother doing writeup on how to do that).
To make lucian1900's comment 100% clear: what MySQL is talking about was not just possible but not that difficult to do with PostgreSQL years ago (via plv8), has always been possible on PostgreSQL with only slight ingenuity required (give me a copy of PostgreSQL from ten years ago and with a couple lines of plpython I will show you efficient range queries of keys from columns of JSON stored as TEXT), and has now for a while been "trivial" due to the addition of native JSON access functions. PostgreSQL has had functional indexes since essentially forever: MySQL seems to have only been given a way to simulate this feature two months ago, by the same people who are working on this JSON support. The real question you should be asking is the opposite: how MySQL handles indexing arbitrary JSON content, and the answer is that given how it is only just as of two months ago capable of indexing limited JSON content, the answer is that MySQL has nothing to say on this matter: PostgreSQL has spent decades pushing the boundary on index technology, while MySQL was trying to make multi-master replication possible (the one use case people have where MySQL seems to ever make sense; though it still isn't clear that this form of replication is theoretically sound :/).
What functional difference does this make? Do you really want to keep your JSON formatting? My guess is all MySQL does is create a shadow JSONB-like format either in-table or in the index.
PostgreSQL doesn't preserve key ordering and strips multiple key/values pairs. I know some systems we have insist on JSON documents having a set order sequence. Also there are often legal reasons why you need the raw, unmodified data preserved.
> I know some systems we have insist on JSON documents having a set order sequence.
I've had to deal with these kinds of systems before, and they are a giant pain in the ass, usually doing some kind of string parsing on the JSON blob or other asinine fake-parsing. While I'd normally agree with you in premise, you're asking for an edge case that doesn't conform to the spec. Additionally, the easiest way to keep the raw, unmodified data preserved, yet still be able to operate and potentially change that data, is to duplicate the data. As soon as that data hits your system, throw it in an immutable TEXT column that your warehouse can suck up and store indefinitely, then normalize and mutate the hell out of it in your operational database, all the while keeping an 'original_data' key back to the raw text. Problem solved.
I can't imagine a sensible use case for a pseudo-JSON literal structured like that. I don't believe I've ever used any parser that would preserve it, and it certainly isn't compatible with JavaScript. Anyone who cares about ordering of multiple values needs to change their schema to use arrays, or start with a less inappropriate format.
The extremely limited "indexing" described by this MySQL documentation is absolutely supported by PostgreSQL's JSON type, and in fact has been for years now if you were willing to tolerate writing a couple lines of plv8 stored procedure. However, if you want the "actual indexing" (on arbitrary content, not just specific keys or lookup strategies) that PostgreSQL provides for JSONB on JSON columns, if you do a functional GiST index on a JSON column cast to a JSONB, you should find that PostgreSQL stores only the JSON but yet can index it "as if" it were JSONB. So, no: absolutely no benefit here.
Its funny how JSON emerged as subset of JavaScript - easy to parse with eval(). But now if you do that its terrible code. So the subset aspect is totally irrelevant now.
XML can't piggyback on the success of anything. It's just a human readable data format, and a cumbersome one at that. It make sense in certain use cases and not in others, but it has to fight it out with other data formats on its own weak merits.
JSON is the object literal format for Javascript, likely the single most widely deployed and used programming language in the world. Until paradigm shifts obsolete the web browser, JSON will be ubiquitous.
Good point, but xml is "cumbersome" or hard and json is not (in the eyes of many). xml was widely hated before json become widely accepted while json is not as widely hated.
I definitely agree that that is the perception. So, apologies to all if my tone earlier implied any condescension.
What intrigues me is why is it so. There is a great article in one of the Programming Pearls books (so, published earlier), that goes over how providing provenance in a data format is highly useful. Yet, by and large, you can not do this in JSON, because comments are disallowed.
I think a relevant paradigm shift partially exists in the form of static typing. Sure, you can still use JSON in a statically typed language, and many do, but it's more cumbersome than in a dynamic one, while the space efficiency and built-in validation of things like protobuf start to look more appealing. Even on the web, statically typed compile-to-JS languages are rising in popularity, though nobody is using protobuf there...
We'll see. I definitely expect JSON to remain popular for many years at the least.
IIRC, Scala's parser/compiler had to have a few little things added to support it even though Scala's syntax mostly supports it without messing with the internals. I could be wrong here, I am a bit fuzzy on the details. Also they sure put in a lot of effort in custom extractors and lots of other features to make it feel like the language supported it natively. But now Scala's XML support is a separate library that is not included with the distribution.
Nah, JSON is just another serialization format, with its own tradeoffs when used inside the database.
Go ahead and read this article from MS SQL server from 2004, about introducing similar features for XML (binary storage, functional indexes) inside the database.
In 10 years, the relational model didn't budge. There are loads of good reasons for why you'd want to decompose data into native DB types, with a strongly-typed schema.
During the last month, two (barely related) Scala programs that I needed to implement in my job were an SVG image generator and an XSD schema creator. I'm thankful that Scala has XML literals, even though when I learned that it did I found it laughable.
I get what you're saying, but these JSON features are actually indicative of a deficiency in the relational model:
The relational model has no performance model. The existence of ever more complicated query optimizers proves this. This is fundamental engineering issue, and NoSQL and JSON in MySQL are engineering hacks that address this issue for specific problems.
It's also a big usability issue. Even if you can tune your queries, indices, and schemas to remove a given performance bottleneck; it could be beyond the skills of users. NoSQL and JSON are indeed simpler.
So it would be nice to come up with a better model instead of one-off hacks, but it's hard. I think it would be cool if you could exhaustively enumerate the queries an application makes, and then the database would somehow generate the schema and indices for you, and also give the time complexity bounds for the queries. But there is kind of a chicken and egg problem there, because the queries depend on the schema.
Huh? I don't see what the big deal is, I think this is standard stuff. I mean, JSON is just another serialization format.
All of these operations are already supported for XML in most databases that were around 5-10 years ago, and the same pitfalls with keeping all your data in XML inside the database also apply to JSON.
Does anyone have a pointer to the actual internal binary format? There have been a zillion attempts to do this efficiently (JSONB, BJSON, BSON, ubjson, MessagePack) and I'm curious which one they chose.
51 comments
[ 1.5 ms ] story [ 105 ms ] thread[0] https://mariadb.com/kb/en/mariadb/dynamic-columns/
[1] http://www.slideshare.net/blueskarlsson/using-json-with-mari...
[2] https://mariadb.com/kb/en/mariadb/virtual-computed-columns/
NoSQL databases are adopting standard SQL interfaces and becoming more uniform.
SQL databases are adopting their own JSON interfaces and becoming more proprietary.
SQL databases are implementing features their users are requesting.
When postgres added JSON operations it brought the sanity and robustness of its database engine to the wild west of document stores.
What's MySQL bringing?
PostgreSQL doesn't preserve key ordering and strips multiple key/values pairs. I know some systems we have insist on JSON documents having a set order sequence. Also there are often legal reasons why you need the raw, unmodified data preserved.
If you business has legal implications on your database format, then why would you trust a NoSQL implementation?
For example - financial transactions: https://twitter.com/seldo/status/413429913715085312
I've had to deal with these kinds of systems before, and they are a giant pain in the ass, usually doing some kind of string parsing on the JSON blob or other asinine fake-parsing. While I'd normally agree with you in premise, you're asking for an edge case that doesn't conform to the spec. Additionally, the easiest way to keep the raw, unmodified data preserved, yet still be able to operate and potentially change that data, is to duplicate the data. As soon as that data hits your system, throw it in an immutable TEXT column that your warehouse can suck up and store indefinitely, then normalize and mutate the hell out of it in your operational database, all the while keeping an 'original_data' key back to the raw text. Problem solved.
https://mariadb.com/kb/en/mariadb/connect-json-table-type/
e.g:
I use this for generating config files, and getting data into pydata tools; no need for a database driver, which is interesting.But JavaScript has the JSON object offering a high-quality, high-performance parser (JSON.parse) and serialiser (JSON.stringify).
[0] https://en.wikipedia.org/wiki/JSONP
JSON is the object literal format for Javascript, likely the single most widely deployed and used programming language in the world. Until paradigm shifts obsolete the web browser, JSON will be ubiquitous.
What intrigues me is why is it so. There is a great article in one of the Programming Pearls books (so, published earlier), that goes over how providing provenance in a data format is highly useful. Yet, by and large, you can not do this in JSON, because comments are disallowed.
We'll see. I definitely expect JSON to remain popular for many years at the least.
In what sense?
Go ahead and read this article from MS SQL server from 2004, about introducing similar features for XML (binary storage, functional indexes) inside the database.
https://msdn.microsoft.com/en-us/magazine/cc188738.aspx
In 10 years, the relational model didn't budge. There are loads of good reasons for why you'd want to decompose data into native DB types, with a strongly-typed schema.
https://prestodb.io/docs/current/functions/json.html
https://cloud.google.com/bigquery/query-reference#jsonfuncti...
This design allows MySQL to implement both without the backwards compatibility concern.
I am speechless.
The relational model has no performance model. The existence of ever more complicated query optimizers proves this. This is fundamental engineering issue, and NoSQL and JSON in MySQL are engineering hacks that address this issue for specific problems.
It's also a big usability issue. Even if you can tune your queries, indices, and schemas to remove a given performance bottleneck; it could be beyond the skills of users. NoSQL and JSON are indeed simpler.
So it would be nice to come up with a better model instead of one-off hacks, but it's hard. I think it would be cool if you could exhaustively enumerate the queries an application makes, and then the database would somehow generate the schema and indices for you, and also give the time complexity bounds for the queries. But there is kind of a chicken and egg problem there, because the queries depend on the schema.
All of these operations are already supported for XML in most databases that were around 5-10 years ago, and the same pitfalls with keeping all your data in XML inside the database also apply to JSON.