3 ms·

I noticed recently that MySQL has a JSON type that stores as structured binary data, while in MariaDB it's just an alias to MEDIUMTEXT and they provide some fun

by mastax 4y ago

I noticed recently that MySQL has a JSON type that stores as structured binary data, while in MariaDB it's just an alias to MEDIUMTEXT and they provide some functions to operate on it as JSON.

Alupis 4y ago
Very interesting, I was not aware one could (or even would want to) store JSON data in a relational database... The JSON alias was added in MariaDB 10.2.7. This was done to make it possible to use JSON columns in statement based replication from MySQL to MariaDB and to make it possible for MariaDB to read mysqldumps from MySQL. JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent. In order to ensure that a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint. This constraint is automatically included for types using the JSON alias from MariaDB 10.4.3.[1] [1] https://mariadb.com/kb/en/json-data-type/ https://mariadb.com/kb/en/json-data-type/
- geraldwhen 4y ago
  I use it to store metadata I don’t need to query for the most part. Saves a lot of time data modeling and managing schemas for that kind of non critical data.
- KronisLV 4y ago
  > I was not aware one could (or even would want to) store JSON data in a relational database... Perhaps it's to borrow some elements from data stores like MongoDB, but whilst keeping most of the system based on the typical table/relation principles. I've actually seen JSON used in PostgreSQL and admittedly I found the approach to be pretty horrible in most cases, performance/functions/syntax regarding the datatype aside. Yet, I can't say that there aren't valid use cases for something like that, even if generally I'd advise against storing data that you actually want to query in a relational DB inside of JSON. Perhaps that's just because I believe that it'd increase complexity unnecessarily in most cases.
djitz 4y ago
I lean on this quite often. I find it really useful to utilize normal relational db things for 98% of your table and then have a column or two where you can store json objects and run queries for deep nested values equals x and things like json contains such and such value, etc.

by mastax 4y ago

I noticed recently that MySQL has a JSON type that stores as structured binary data, while in MariaDB it's just an alias to MEDIUMTEXT and they provide some functions to operate on it as JSON.

Alupis 4y ago

Very interesting, I was not aware one could (or even would want to) store JSON data in a relational database... The JSON alias was added in MariaDB 10.2.7. This was done to make it possible to use JSON columns in statement based replication from MySQL to MariaDB and to make it possible for MariaDB to read mysqldumps from MySQL. JSON is an alias for LONGTEXT introduced for compatibility reasons with MySQL's JSON data type. MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at least equivalent. In order to ensure that a valid json document is inserted, the JSON_VALID function can be used as a CHECK constraint. This constraint is automatically included for types using the JSON alias from MariaDB 10.4.3.[1] [1] https://mariadb.com/kb/en/json-data-type/ https://mariadb.com/kb/en/json-data-type/

geraldwhen 4y ago
I use it to store metadata I don’t need to query for the most part. Saves a lot of time data modeling and managing schemas for that kind of non critical data.
KronisLV 4y ago
> I was not aware one could (or even would want to) store JSON data in a relational database... Perhaps it's to borrow some elements from data stores like MongoDB, but whilst keeping most of the system based on the typical table/relation principles. I've actually seen JSON used in PostgreSQL and admittedly I found the approach to be pretty horrible in most cases, performance/functions/syntax regarding the datatype aside. Yet, I can't say that there aren't valid use cases for something like that, even if generally I'd advise against storing data that you actually want to query in a relational DB inside of JSON. Perhaps that's just because I believe that it'd increase complexity unnecessarily in most cases.

djitz 4y ago

I lean on this quite often. I find it really useful to utilize normal relational db things for 98% of your table and then have a column or two where you can store json objects and run queries for deep nested values equals x and things like json contains such and such value, etc.