Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL vs PostgreSQL JSON search features

Tags:

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.)

like image 649
SlideM Avatar asked Jan 03 '17 12:01

SlideM


1 Answers

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.

like image 158
e4c5 Avatar answered Sep 20 '22 00:09

e4c5