11

I’m not talking about dynamic columns, I’m asking about native column JSON data type. In simple words, can I run the following code on any MariaDB version?

CREATE TABLE example (names JSON);

As far as I know, it doesn’t but I’m still not sure since there’s a lot of topics talking about JSON support on MariaDB for ages, but none said it’s finally implemented.

-- Update --

Just found three issues still open on MariaDB Jira about JSON data type support, which means it's still not yet implemented, right?

  • That update should not be in the question, that's the answer. You should move that out and self-answer. @Omranic. – Evan Carroll Jun 22 '17 at 5:52
  • > JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. mariadb.com/kb/en/library/json-data-type – zx1986 Nov 23 '17 at 3:38
6

JSON support will come to MariaDB 10.2. See the official MariaDB blog post from 28.02.2017. There are some example SQL statements and validation.

JSON is fast becoming the standard format for data interchange and for unstructured data, and MariaDB 10.2 adds a range on JSON supporting functions, even though a JSON datatype isn't implemented yet. There are some reasons why there isn't a JSON datatype, but one is that there are actually not that many advantages to that as JSON is a text-based format. This blog post aims to describe JSON and the use cases for it, as well as to describe the MariaDB 10.2 JSON functions and uses for these, as well as showing some other additions to MariaDB 10.2 that are useful for JSON processing.

  • 1
    Till 10.2.6,not support native JSON yet. – kittygirl Jun 22 '17 at 3:29
4

It depends on what you mean when you say "data type". Some databases like PostgreSQL, have a JSON data type that permits full text search, a binary storage mechanism, indexing, and full suite of operators to access the data. Maria doesn't yet have that. The data type specifically is being tracked by MDEV-9144.

Even that though, brings a very limited type to MySQL, from one of the bug maintainers,

JSON data type directly contradicts SQL standard, that says, that JSON_* functions take a string as an argument. Also, speed-wise MariaDB does not need binary JSON, according to our benchmarks, our JSON parser is as fast on text JSON as MySQL on binary JSON. That is, in MariaDB one could VARCHAR or TEXT for JSON. If a validation is needed, one can do it with a CHECK constraint:

my_json_column TEXT CHECK (JSON_VALID(my_json_column))

We'll add JSON "type" for MySQL compatibility, though.

From my reading, that's not exactly the point of binary JSON let's refer to the MySQL docs

The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

Again, PostgreSQL's jsonb does a lot more than that.

jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

tldr; Maria DB doesn't have a JSON type yet. Even when it gets the "type" it's just a thin wrapper over a text-validation (like PostgreSQL's json type). There are no plans for a binary JSON type (like PostgreSQL's jsonb) because the developers seemingly don't understand the advantages.

  • The user really does not care whether JSON in Postgres is stored composed or decomposed, whether json is binary or not. Important is for JSON a) what you put it, you get out, and there is a possibility to check for valid input. b) you can index and search for parts of JSON. 3) There are functions to operate on it. – Vladislav Vaintroub Nov 22 '18 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.