How to return value of json string in mysql

Question

Let's say that we have the following table 'articles':

id_article | article_title
     1     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}
     2     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}
     3     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}
     4     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}
     5     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}
     6     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}
     7     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}

And let's say that we also have the following table 'seen':

id_seen | id_item_seen
   1    |      3
   2    |      5
   3    |      5
   4    |      3
   5    |      3
   6    |      3
   7    |      3
   8    |      2
   9    |      2
   10   |      4
   11   |      1
   12   |      7
   13   |      7

I've executed the following SQL query:

select articles.id_article, articles.id_article, count(*) as seens from seen
inner join articles on articles.id_article = seen.id_item_seen
group by articles.id_article
order by seens desc

This will return the following result:

id_article | article_title                                                     | seens
     1     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"} |   1
     2     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"} |   2
     3     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"} |   5
     4     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"} |   1
     5     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"} |   2
     6     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"} |   0
     7     | {"ar":"Arabic value", "en":"English value", "tr":"Turkish value"} |   2

This is ok, but I want to return not all of the stored JSON {}, but just a specified value of desired key (for example: "ar") as the following:

     1     | Arabic value |   1
     2     | Arabic value |   2
     3     | Arabic value |   5
     4     | Arabic value |   1
     5     | Arabic value |   2
     6     | Arabic value |   0
     7     | Arabic value |   2

I know we could do this with PHP using json_decode() function, but How could we achieve this with SQL query?

Your mysql version?

Mihai
– Mihai

2017-07-29 15:26:19 +00:00
Commented Jul 29, 2017 at 15:26 — Mihai
– Mihai, Commented Jul 29, 2017 at 15:26
my MySQL version is 5.6.35

nael_d
– nael_d

2017-07-29 15:31:31 +00:00
Commented Jul 29, 2017 at 15:31 — nael_d
– nael_d, Commented Jul 29, 2017 at 15:31

Mihai · Accepted Answer · 2017-07-29 15:48:11Z

1

Use SBTRING_INDEX in mysql < 5.7.Replace that json with your column name.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('{"ar":"Arabic value", "en":"English value", "tr":"Turkish value"}','ar":"',-1),'"',1)

See it working.

answered Jul 29, 2017 at 15:48

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nael_d Over a year ago

Hola! That's what I'm looking for! Very THANK YOU! <3

Sami Kuhmonen · Accepted Answer · 2017-07-29 15:28:28Z

1

If you're using MySQL 5.7 and the column is of JSON type (as it should be) you can retrieve it with

select articles.id_article, articles.article_title->'$.ar', count(*) as seens from seen
inner join articles on articles.id_article = seen.id_item_seen
group by articles.id_article
order by seens desc

See the documentation of the JSON type.

If you have an older version of MySQL then it would be better to handle the extraction outside the server to make sure it's handled properly.

answered Jul 29, 2017 at 15:28

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

1 Comment

nael_d Over a year ago

OOOOOOHHH :-( my mysql version is 5.6.35 I see that it's in-executable query. However, thank you :-)

Collectives™ on Stack Overflow

How to return value of json string in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related