7 ms·

JSON Labs Release: Native JSON Data Type and Binary Format

jstsch 11y ago
Nice! Will this also be a feature of MariaDB?
- michaelmior 11y ago
  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. [0] https://mariadb.com/kb/en/mariadb/dynamic-columns/ https://mariadb.com/kb/en/mariadb/dynamic-columns/ [1] http://www.slideshare.net/blueskarlsson/using-json-with-mariadb-and-mysql http://www.slideshare.net/blueskarlsson/using-json-with-mari... [2] https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ https://mariadb.com/kb/en/mariadb/virtual-computed-columns/
threeseed 11y ago
Such a strange world we live in. NoSQL databases are adopting standard SQL interfaces and becoming more uniform. SQL databases are adopting their own JSON interfaces and becoming more proprietary.
- ryanpetrich 11y ago
  NoSQL databases are implementing features their users are requesting. SQL databases are implementing features their users are requesting.
hliyan 11y ago
They should lead with this revelation: You can... [create] indexes on values within the JSON columns Ability to index changes everything.
- eknkc 11y ago
  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?
  - hliyan 11y ago
    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'; [1]: http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html
    - saurik 11y ago
      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).
  - lucian1900 11y ago
    You can create expression-based indexes on anything. That has been possible with regular JSON for a while now and it still works with JSONB.
  - saurik 11y ago
    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 :/).
ris 11y ago
We already have a not-particularly-transaction-safe JSON database in MongoDB. 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?
- dozzie 11y ago
  The usual thing, the try to catch up with Postgres from five years ago.
  - threeseed 11y ago
    PostgreSQL only supports indexing for JSONB so actually MySQL is ahead here.
    - ris 11y ago
      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.
      - threeseed 11y ago
        According to here: http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html 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.
        mbesto 11y ago
        > Also there are often legal reasons 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 https://twitter.com/seldo/status/413429913715085312
el33th4xx0r 11y ago
Now, i'm waiting for sqlite to add native support on json & xml data type
mathnode 11y ago
JSON support is also provided by the CONNECT storage engine, which I think is much simpler. https://mariadb.com/kb/en/mariadb/connect-json-table-type/ https://mariadb.com/kb/en/mariadb/connect-json-table-type/ e.g: SQL > CREATE TABLE junk.j1 (a int default null) ENGINE=CONNECT DEFAULT CHARSET=utf8 table_type=JSON File_name='j1.json'; SQL > insert into junk.j1 (values) (1); $ ls $(mysql -NBe "select @@datadir")/junk db.opt j1.frm j1.json $ cat $(mysql -NBe "select @@datadir")/junk/j1.json [ { "a": 1 } ] I use this for generating config files, and getting data into pydata tools; no need for a database driver, which is interesting.
ape4 11y ago
I feel as soon as every database supports JSON the world will move to a different data format. Like I feel a bit sad for Scala having native XML.
- applecore 11y ago
  JavaScript has pretty good, native JSON support.
  - sopooneo 11y ago
    What do we mean by that? Hasn't JavaScript always had native JSON support since whenever "eval()" was included?
    - sinatra 11y ago
      I think applecore's comment was tongue-in-cheek considering JSON is JavaScript Object Notation and was originally derived from JavaScript.
    - TazeTSchnitzel 11y ago
      eval() isn't quite the same. It's unsafe for starters: it permits any JS code, not just JSON. But JavaScript has the JSON object offering a high-quality, high-performance parser (JSON.parse) and serialiser (JSON.stringify).
      - ape4 11y ago
        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.
        heydenberk 11y ago
        The subset aspect is indeed still relevant. JSONP callbacks[0] make use of JSON's just-JavaScript nature. [0] https://en.wikipedia.org/wiki/JSONP https://en.wikipedia.org/wiki/JSONP
ImJasonH 11y ago
Can anybody explain why the function is "jsn_extract" instead of "json_extract"? I thought it was a typo at first...
- scient 11y ago
  Because some people should not be allowed to name things, ever.
- jakejake 11y ago
  I thought that was a bit annoying too. And it's also underscore delimited, when there is already a similar function for XML which is ExtractValue()
- haberman 11y ago
  I was wondering the same thing, especially since there is precedent for the JSON_EXTRACT name in SQL: https://prestodb.io/docs/current/functions/json.html https://prestodb.io/docs/current/functions/json.html https://cloud.google.com/bigquery/query-reference#jsonfunctions https://cloud.google.com/bigquery/query-reference#jsonfuncti...
- morgo 11y ago
  There may be some chance that json_* functions become a standard, but have different call parameters. This design allows MySQL to implement both without the backwards compatibility concern.
Thiz 11y ago
Codd must be rolling in his grave. I am speechless.
- chubot 11y ago
  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.
- kbaker 11y ago
  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.
ccleve 11y ago
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.
ademarre 11y ago
Neat. I don't see any mention of it, but it would be nice if it also supported JSON PATCH for UPDATEs: https://tools.ietf.org/html/rfc6902 https://tools.ietf.org/html/rfc6902
deleted 11y ago
[deleted]

Nice! Will this also be a feature of MariaDB?

michaelmior 11y ago
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. [0] https://mariadb.com/kb/en/mariadb/dynamic-columns/ https://mariadb.com/kb/en/mariadb/dynamic-columns/ [1] http://www.slideshare.net/blueskarlsson/using-json-with-mariadb-and-mysql http://www.slideshare.net/blueskarlsson/using-json-with-mari... [2] https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ https://mariadb.com/kb/en/mariadb/virtual-computed-columns/

Such a strange world we live in. NoSQL databases are adopting standard SQL interfaces and becoming more uniform. SQL databases are adopting their own JSON interfaces and becoming more proprietary.

ryanpetrich 11y ago
NoSQL databases are implementing features their users are requesting. SQL databases are implementing features their users are requesting.

hliyan 11y ago

They should lead with this revelation: You can... [create] indexes on values within the JSON columns Ability to index changes everything.

eknkc 11y ago
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?
- hliyan 11y ago
  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'; [1]: http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html
  - saurik 11y ago
    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).
- lucian1900 11y ago
  You can create expression-based indexes on anything. That has been possible with regular JSON for a while now and it still works with JSONB.
- saurik 11y ago
  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 :/).

ris 11y ago

We already have a not-particularly-transaction-safe JSON database in MongoDB. 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?

dozzie 11y ago
The usual thing, the try to catch up with Postgres from five years ago.
- threeseed 11y ago
  PostgreSQL only supports indexing for JSONB so actually MySQL is ahead here.
  - ris 11y ago
    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.
    - threeseed 11y ago
      According to here: http://www.postgresql.org/docs/9.4/static/datatype-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html 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.
      - mbesto 11y ago
        > Also there are often legal reasons 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 https://twitter.com/seldo/status/413429913715085312

el33th4xx0r 11y ago

Now, i'm waiting for sqlite to add native support on json & xml data type

mathnode 11y ago

JSON support is also provided by the CONNECT storage engine, which I think is much simpler. https://mariadb.com/kb/en/mariadb/connect-json-table-type/ https://mariadb.com/kb/en/mariadb/connect-json-table-type/ e.g: SQL > CREATE TABLE junk.j1 (a int default null) ENGINE=CONNECT DEFAULT CHARSET=utf8 table_type=JSON File_name='j1.json'; SQL > insert into junk.j1 (values) (1); $ ls $(mysql -NBe "select @@datadir")/junk db.opt j1.frm j1.json $ cat $(mysql -NBe "select @@datadir")/junk/j1.json [ { "a": 1 } ] I use this for generating config files, and getting data into pydata tools; no need for a database driver, which is interesting.

ape4 11y ago

I feel as soon as every database supports JSON the world will move to a different data format. Like I feel a bit sad for Scala having native XML.

applecore 11y ago
JavaScript has pretty good, native JSON support.
- sopooneo 11y ago
  What do we mean by that? Hasn't JavaScript always had native JSON support since whenever "eval()" was included?
  - sinatra 11y ago
    I think applecore's comment was tongue-in-cheek considering JSON is JavaScript Object Notation and was originally derived from JavaScript.
  - TazeTSchnitzel 11y ago
    eval() isn't quite the same. It's unsafe for starters: it permits any JS code, not just JSON. But JavaScript has the JSON object offering a high-quality, high-performance parser (JSON.parse) and serialiser (JSON.stringify).
    - ape4 11y ago
      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.
      - heydenberk 11y ago
        The subset aspect is indeed still relevant. JSONP callbacks[0] make use of JSON's just-JavaScript nature. [0] https://en.wikipedia.org/wiki/JSONP https://en.wikipedia.org/wiki/JSONP

ImJasonH 11y ago

Can anybody explain why the function is "jsn_extract" instead of "json_extract"? I thought it was a typo at first...

scient 11y ago
Because some people should not be allowed to name things, ever.
jakejake 11y ago
I thought that was a bit annoying too. And it's also underscore delimited, when there is already a similar function for XML which is ExtractValue()
haberman 11y ago
I was wondering the same thing, especially since there is precedent for the JSON_EXTRACT name in SQL: https://prestodb.io/docs/current/functions/json.html https://prestodb.io/docs/current/functions/json.html https://cloud.google.com/bigquery/query-reference#jsonfunctions https://cloud.google.com/bigquery/query-reference#jsonfuncti...
morgo 11y ago
There may be some chance that json_* functions become a standard, but have different call parameters. This design allows MySQL to implement both without the backwards compatibility concern.

Thiz 11y ago

Codd must be rolling in his grave. I am speechless.

chubot 11y ago
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.
kbaker 11y ago
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.

ccleve 11y ago

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.

ademarre 11y ago

Neat. I don't see any mention of it, but it would be nice if it also supported JSON PATCH for UPDATEs: https://tools.ietf.org/html/rfc6902 https://tools.ietf.org/html/rfc6902

deleted 11y ago

[deleted]