MySQL vs PostgreSQL JSON search features

Question

I have been searching for a blog post or a feature matrix which compares MySQL and PostgreSQL by their JSON capabilities. I found a good feature matrix for Postgres but not for MySQL. Also there are a bunch posts like this which compare the two but are outdated and/or not really detailed. So far I only found out that both can save data using the JSONB data type, are able to index on fields within the JSON for improved query performances and that they can full text search on those fields. But not how well and what drawbacks there are. What is a deciding factor to favour MySQL over Postgres and vice versa for their JSON search features?

(This question was posted when MySQL 5.7 and PostgreSQL 9.6 were the current versions; this sort of thing changes quickly so later readers should keep that in mind.)

Trueman

Good question but likely to be closed any second as too broad and leading to subjective answers. This used to be a valid question-style in SO in the old days but the current voque for moderating means you will probably be disappointed even though this is an excellent question for 2017. – Rugen

@VanquishedWombat So tempted to flag your comment "not constructive" just to tweak you :p but honestly I think this is specific enough to be quite useful. Dunno of any such resources, I'd be interested to see them myself. – Gamba

@CraigRinger - its one of the annoyances of the evolution of SO. If you look for similar questions about C vs C# you will find questions that were allowed to live and generated really useful input. However, if you request them to be closed as subjective / off topic / too broad then you get shot down. It seems that the SO content is becoming stale and less useful for new technologies. If you consider liefcycle of tech it always starts with open questions which become more specific over time. Moderators seem not to appreciate that point. Though I may yet eat my hat. – Rugen

Honestly I agree with the "too broad" more often than not. If I can write a book on it, it's not a good thing for here, where it'll likely just bit-rot and generate endless argument as well. Have your arguments on wikipedia or wherever. This is specific though, and can be addressed in a reasonable answer. Whether it will be... – Gamba

Answer 1

What is a deciding factor to favour MySQL over Postgres and vice versa for their JSON search features?

Well, the fact that postgresql JSONB has several functions that can return rows while mysql at best can only return arrays is reason enough to stick to postgresql rather than mysql.

If you think, mysql can return rows from JSON please answer this question from October that's still unanswered: Convert JSON array in MySQL to rows

Mysql isn't so good at subqueries as postgresql, but the JSON functionality limit your ability to perform joins against other tables. In contrast, postgresql has a whole heap of functions that can return rows.

Matrices

Latest version of MySql ( 8.0.4 ) now supports the ability to return table based result sets from JSON - dev.mysql.com/doc/refman/8.0/en/json-table-functions.html. – Quartana

Answer 2

imho, it's not about features, but rather performance (and Postgres has MySQL beat on both fronts). The reason for this is that Postgres supported JSON columns well before MySQL and MySQL STILL doesn't support indexing json columns and their values.

So, forget the rest - Postgres has it beat, hands down. If you're looking for an RDBMS with document-store like features, Pos

Machinegun