如何在mysql中返回json字符串的值

Question

假设我们有下面的表“文章”：

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"}

让我们假设我们也有下面的表‘看到’：

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

我执行了以下SQL查询：

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

这将返回以下结果：

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

这是可以的，但我不想返回所有存储的JSON {}，而是只返回所需键的指定值(例如："ar")，如下所示：

     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

我知道我们可以使用json_decode()函数在PHP中做到这一点，但是我们如何通过SQL query来实现呢？

Answer 1

在mysql <5.7中使用SBTRING_INDEX，用您的列名替换该json。

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

See it working.

Answer 2

如果您使用的是JSON5.7，并且列是MySQL类型(正如应该的那样)，则可以使用以下命令检索它

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

请参阅documentation of the JSON type。

如果您使用的是较旧版本的MySQL，那么最好在服务器外部处理提取，以确保它得到正确处理。