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

Question

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

score 3 · Accepted Answer · answered Dec 05 '17 at 20:22

3

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.

answered Dec 05 '17 at 20:22

Bill Karwin

538,548
86
673
828

Thanks a ton Bill, you saved my day. '$[*].title' is what I was looking for. I did go through the documentation, but wasn't able to figure out how to use it on JSON arrays – NKP Dec 06 '17 at 03:24
*Hey Bill,* remember what you told me not too long ago? ;-) – Funk Forty Niner Dec 06 '17 at 14:58
@Fred-ii- You got me! I am breaking my own rule. But at least this is not the 1000th time I've seen this question. – Bill Karwin Dec 06 '17 at 16:36
@BillKarwin Hehe, no worries Bill. There are exceptions ;-) but yeah, I caught you *lol!* - Cheers. – Funk Forty Niner Dec 06 '17 at 16:59

score 0 · Answer 2 · answered Dec 05 '17 at 19:55

0

This looks like a database design issue. It seems that you're using a json column when you should really be using another table to store these results as individual columns.

answered Dec 05 '17 at 19:55

cjsfj

29
5

My initial DB design did have all the values in multiple columns, but this is a one to many DB relationship I'm dealing it. So, if I start saving "id", "title" and "score" in separate columns, and they've a lot of multiple values, I'll end up saving them in too many rows – NKP Dec 06 '17 at 03:17

score 0 · Answer 3 · answered Dec 05 '17 at 20:21

0

you can query like below

create table testTableforus (datapoint json);
insert into testTableforus
values
 ('{"id": "26", "title": "similar1", "score": "0.97"}'), 
 ('{"id": "27", "title": "similar2", "score": "0.87"}'), 
 ('{"id": "28", "title": "similar2", "score": "0.87"}');

 select datapoint->"$.title" from testTableforus;

drop table testTableforus;

See working demo

answered Dec 05 '17 at 20:21

DhruvJoshi

17,041
6
41
60

Thanks bhai, but if you observe closely, I've all the data in a single record and not multiple rows. Basically my data is an array of JSON object, and not a single JSON object like you've demonstrated – NKP Dec 06 '17 at 03:19

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

3 Answers3