Search code examples
mysqlsqljsonmysql-json

How can I extract all values of a particular attribute within elements inside a JSON array in MySQL?

I've the below data stored in a column of MySQL table which is of "json" data type (MySQL v5.7.9)

[{"id": "26", "title": "similar1", "score": "0.97"}, {"id": "27", "title": "similar2", "score": "0.87"}, {"id": "28", "title": "similar2", "score": "0.87"}]

I need a MySQL query which will return me all the "title" values i.e. similar1, similar2 and similar3 and so on

Solution

  • Use JSON_EXTRACT(). 

    mysql> create table t ( j json );

mysql> insert into t set j = '[{"id": "26", "title": "similar1", "score": "0.97"}, {"id": "27", "title": "similar2", "score": "0.87"}, {"id": "28", "title": "similar2", "score": "0.87"}]';

mysql> select json_extract(j, '$[*].title') from t;
+--------------------------------------+
| json_extract(j, '$[*].title')        |
+--------------------------------------+
| ["similar1", "similar2", "similar2"] |
+--------------------------------------+

    If you want to use JSON data in MySQL, you should read the docs on Functions that Search JSON Values, and learn to use the JSON search expressions.