4 ms·

Has anyone worked with MySQL JSON data types in production? For most projects, I prefer working in SQL via a query builder or ORM for abstraction, but find a fe

Has anyone worked with MySQL JSON data types in production? For most projects, I prefer working in SQL via a query builder or ORM for abstraction, but find a few features that would benefit from denormalized JSON storage.

toomuchtodo 9y ago
Use PostgreSQL. https://www.postgresql.org/docs/10/static/datatype-json.html https://www.postgresql.org/docs/10/static/datatype-json.html
- mat_keep 9y ago
  or not, as the case may be https://www.mongodb.com/compare/mongodb-postgresql https://www.mongodb.com/compare/mongodb-postgresql
  - furicane 9y ago
    You're aware that the vendor will post gospel about their product on their own website? I'm not using Postgres, I use MySQL, but I can assert with 100% certainty that most of the "comparison" on that URL is just marketing bullshit. You're a prime example that this new-age bullshit works and that's what's worrying.
    - mat_keep 9y ago
      great. Do i declare i worked at mysql for 5 years as well? Both before and after Oracle acquisition
      - furicane 9y ago
        How's that relevant? You're neither lead architect nor does your employment at MySQL / Oracle mean anything. Many incapable developers score positions at big companies. Without facts, maths and measurement you can be Linus Torvalds for all I care. The article at the URL provided is simply marketing ploy. I'm experienced enough to notice what's wrong with the comparison, but I won't take that article apart to prove anything. If you believe Mongo is a product that helps you - perfect. Now, if you're thinking you'll convince me that it's great by berating another great product - well, that raises a red flag. Who says I've to use a single software vendor anyway?
        mat_keep 9y ago
        it was the reference to me as "You're a prime example that this new-age bullshit works and that's what's worrying". The same statements were leveled at anyone from mysql 10-15 years ago. I'm glad mysql works well for your use-case
  - znep 9y ago
    Are you the Mat Keep who is Director of Product Marketing at MongoDB? If so then, FWIW, people don't often respond well to marketing links posted without identifying your connection to the company.
    - mat_keep 9y ago
      yes, my bad, i should have done that
  - Tostino 9y ago
    That was a pretty trash piece of marketing.
furicane 9y ago
I've been using MySQL 5.7 since its release in production. I used to store JSON data in a blob, but when they released JSON support - I just couldn't wait to use it. It's working really, really good. JSON support solves a ton of problems we used to have, and we used EAV model to tackle those problems (I won't get into details, the discussion will go the other way). I deal with a few hundred MySQL deployments ranging from 1GB dataset size to several terabytes, with multimaster and MySQL cluster setup to in-app handled sharding. There are several notable pieces of software that never caused any problems in our use scenario, and MySQL is one of them. The other is nginx. Can't really remember the third. I often wondered why anyone would use MongoDB or similar, but I always keep forgetting that developers are usually inexperienced hipsters who are looking for magic unicorn to solve their lack of knowledge / experience.
- pizza234 9y ago
  > I often wondered why anyone would use MongoDB or similar, but I always keep forgetting that developers are usually inexperienced hipsters You can do a basic google search, and you will find out use cases where document-based data storages are advantageous compare to relational ones.
  - furicane 9y ago
    So what you're saying that if I find something on google, it's undoubtedly true and I should blindly believe it? For every one of those use cases, I can provide a counter-argument. And there are many more who can do the same.
    - scarface74 9y ago
      I used RDMSs for 15 years before ever using Mongo. I fell in love with Mongo from the first time I used it with a pre-existing system. The beauty of just being able to insert and retrieve my whole C# object graph with one statement was a thing of beauty. I'm a Domain Driven Design true believer. The thought of just being able to store my entire domain model without the machinations of the object relational impedance mismatches was glorious. I use C# so my document collections are strongly typed C# objects and thanks to the Mongo C# Linq provider, my queries are also strongly typed.
chrisan 9y ago
We switched over some non-essential data to json. All of it was for stuff that we don't index, join, or SQL search (elasticsearch still indexes it). It is always just the display data on details page Not sure if thats a good use case, but it cleaned up our tables and given the data fields can vary from customer to customer its a nice flexibility. Previously it was an Entity-attribute-value setup for these fields (which was very slow after we got into millions of rows) and we were considering a NoSQL option

toomuchtodo 9y ago

Use PostgreSQL. https://www.postgresql.org/docs/10/static/datatype-json.html https://www.postgresql.org/docs/10/static/datatype-json.html

mat_keep 9y ago
or not, as the case may be https://www.mongodb.com/compare/mongodb-postgresql https://www.mongodb.com/compare/mongodb-postgresql
- furicane 9y ago
  You're aware that the vendor will post gospel about their product on their own website? I'm not using Postgres, I use MySQL, but I can assert with 100% certainty that most of the "comparison" on that URL is just marketing bullshit. You're a prime example that this new-age bullshit works and that's what's worrying.
  - mat_keep 9y ago
    great. Do i declare i worked at mysql for 5 years as well? Both before and after Oracle acquisition
    - furicane 9y ago
      How's that relevant? You're neither lead architect nor does your employment at MySQL / Oracle mean anything. Many incapable developers score positions at big companies. Without facts, maths and measurement you can be Linus Torvalds for all I care. The article at the URL provided is simply marketing ploy. I'm experienced enough to notice what's wrong with the comparison, but I won't take that article apart to prove anything. If you believe Mongo is a product that helps you - perfect. Now, if you're thinking you'll convince me that it's great by berating another great product - well, that raises a red flag. Who says I've to use a single software vendor anyway?
      - mat_keep 9y ago
        it was the reference to me as "You're a prime example that this new-age bullshit works and that's what's worrying". The same statements were leveled at anyone from mysql 10-15 years ago. I'm glad mysql works well for your use-case
- znep 9y ago
  Are you the Mat Keep who is Director of Product Marketing at MongoDB? If so then, FWIW, people don't often respond well to marketing links posted without identifying your connection to the company.
  - mat_keep 9y ago
    yes, my bad, i should have done that
- Tostino 9y ago
  That was a pretty trash piece of marketing.

furicane 9y ago

I've been using MySQL 5.7 since its release in production. I used to store JSON data in a blob, but when they released JSON support - I just couldn't wait to use it. It's working really, really good. JSON support solves a ton of problems we used to have, and we used EAV model to tackle those problems (I won't get into details, the discussion will go the other way). I deal with a few hundred MySQL deployments ranging from 1GB dataset size to several terabytes, with multimaster and MySQL cluster setup to in-app handled sharding. There are several notable pieces of software that never caused any problems in our use scenario, and MySQL is one of them. The other is nginx. Can't really remember the third. I often wondered why anyone would use MongoDB or similar, but I always keep forgetting that developers are usually inexperienced hipsters who are looking for magic unicorn to solve their lack of knowledge / experience.

pizza234 9y ago
> I often wondered why anyone would use MongoDB or similar, but I always keep forgetting that developers are usually inexperienced hipsters You can do a basic google search, and you will find out use cases where document-based data storages are advantageous compare to relational ones.
- furicane 9y ago
  So what you're saying that if I find something on google, it's undoubtedly true and I should blindly believe it? For every one of those use cases, I can provide a counter-argument. And there are many more who can do the same.
  - scarface74 9y ago
    I used RDMSs for 15 years before ever using Mongo. I fell in love with Mongo from the first time I used it with a pre-existing system. The beauty of just being able to insert and retrieve my whole C# object graph with one statement was a thing of beauty. I'm a Domain Driven Design true believer. The thought of just being able to store my entire domain model without the machinations of the object relational impedance mismatches was glorious. I use C# so my document collections are strongly typed C# objects and thanks to the Mongo C# Linq provider, my queries are also strongly typed.

chrisan 9y ago

We switched over some non-essential data to json. All of it was for stuff that we don't index, join, or SQL search (elasticsearch still indexes it). It is always just the display data on details page Not sure if thats a good use case, but it cleaned up our tables and given the data fields can vary from customer to customer its a nice flexibility. Previously it was an Entity-attribute-value setup for these fields (which was very slow after we got into millions of rows) and we were considering a NoSQL option