

JSON Labs Release: Native JSON Data Type and Binary Format - johnchristopher
http://mysqlserverteam.com/json-labs-release-native-json-data-type-and-binary-format/

======
ape4
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.

~~~
mycelium
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.

~~~
taeric
This is.... an interesting perspective. Why couldn't xml "piggyback" off of
the success of html, for example?

~~~
shaneofalltrad
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.

~~~
taeric
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.

------
threeseed
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
NoSQL databases are implementing features their users are requesting.

SQL databases are implementing features their users are requesting.

------
mathnode
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.

------
hliyan
They should lead with this revelation:

    
    
       You can... [create] indexes on values within the JSON columns
    

Ability to index changes everything.

~~~
eknkc
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
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
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).

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

~~~
morgo
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.

------
ris
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
The usual thing, the try to catch up with Postgres from five years ago.

~~~
threeseed
PostgreSQL only supports indexing for JSONB so actually MySQL is ahead here.

~~~
ris
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
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
> _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)

------
ademarre
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)

------
ccleve
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.

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

------
jstsch
Nice! Will this also be a feature of MariaDB?

~~~
michaelmior
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-
mari...](http://www.slideshare.net/blueskarlsson/using-json-with-mariadb-and-
mysql)

[2] [https://mariadb.com/kb/en/mariadb/virtual-computed-
columns/](https://mariadb.com/kb/en/mariadb/virtual-computed-columns/)

------
Thiz
Codd must be rolling in his grave.

I am speechless.

~~~
chubot
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.

