JSON Labs Release: Native JSON Data Type and Binary Format | Hacker News Reader

JSON Labs Release: Native JSON Data Type and Binary Format

mysqlserverteam.com

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.

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.

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.

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

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.

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.

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.

JavaScript has pretty good, native JSON support.

What do we mean by that? Hasn't JavaScript always had native JSON support since whenever "eval()" was included?

I think applecore's comment was tongue-in-cheek considering JSON is JavaScript Object Notation and was originally derived from JavaScript.

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

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.

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

In old browsers, yes. The much more secure CORS is now an option, however.

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.

> Like I feel a bit sad for Scala having native XML.

In what sense?

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.

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.

NoSQL databases are implementing features their users are requesting.

SQL databases are implementing features their users are requesting.

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/

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.

They should lead with this revelation:

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

Ability to index changes everything.

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?

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 :/).

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

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

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.

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

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.

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://cloud.google.com/bigquery/query-reference#jsonfuncti...

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

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?

The usual thing, the try to catch up with Postgres from five years ago.

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

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.

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.

According to here: 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.

> 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

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

Legal reasons such as? Not doubting you, genuinely curious.

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.

Why does that mean MySQL is ahead? Postgres supports JSONB and JSON. Aren't they just equal now?

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

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.

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

Nice! Will this also be a feature of MariaDB?

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/

[1] http://www.slideshare.net/blueskarlsson/using-json-with-mari...

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

Codd must be rolling in his grave.

I am speechless.

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.

Reply on news.ycombinator.com