MySQL vs. PostgreSQL JSON Search Functions

Question

I was looking for a blog post or feature matrix that compares MySQL and PostgreSQL with their JSON capabilities. I found a good functional matrix for Postgres , but not for MySQL. There are also columns, such as this , that compare the two, but are outdated and / or not very detailed. So far, I have only learned that both can save data using the JSONB data type, can index by JSON fields to improve query performance and so that they can perform full-text searches on these fields. But not how good and what the disadvantages are. What is the decisive factor for supporting MySQL over Postgres and vice versa for their JSON lookup functions?

(This question was posted when MySQL 5.7 and PostgreSQL 9.6 were current versions, things change quickly, so readers should keep this in mind.)

Answer 1

What is the decisive factor for supporting MySQL through Postgres and vice versa for their JSON lookup functions?

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

If you think mysql can return rows from JSON, please answer this question from October, which has not yet answered: Converting a JSON array in MySQL to strings

Mysql is not as good at subqueries as postgresql, but JSON functions limit your ability to perform joins with other tables. In contrast, postgresql has a whole bunch of functions that can return strings.