17

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?

2
  • That update should not be in the question, that's the answer. You should move that out and self-answer. @Omranic. Jun 22, 2017 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, 2017 at 3:38

2 Answers 2

11

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.

4
  • 2
    Till 10.2.6,not support native JSON yet.
    – kittygirl
    Jun 22, 2017 at 3:29
  • 1
    They store the json as a plain text only as of 10.3 Sep 4, 2019 at 9:56
  • 3
    still in 2020-04 with MariaDB 10.5.2-beta, JSON is still just an alias for LONGTEXT, they don't have a proper JSON datatype (yet?) - but MySQL has supported it since 2015-08
    – hanshenrik
    Apr 1, 2020 at 23:21
  • Well that's a deal-breaker :-/
    – geoidesic
    Jan 31, 2022 at 20:48
9

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.

1
  • 1
    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. Nov 22, 2018 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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