Mysql json 数据类型计数和 groupby

Question

所以从 mysql 5.7 开始，我现在可以使用 json 数据类型，这在您使用像 angularjs 这样的框架时非常有用。

现在因为我对此很陌生(而且这个数据类型也很新)我想知道我是否可以对该数据集进行一些简单的操作。

例如，我在我的数据库中保存了一个 json，其中每个对象都包含以下字段:

name
country
phone_num

我想做的是，我希望总结出每个国家/地区通常有多少人可以使用这样的选择

select country, count(*) as num_residents from mytable

但是我不太确定如何在具有 json 数据类型的表上使用这种选择语句。

谁能帮帮我？

更新

好吧，我的 table 看起来像这样:

id  int(11) AI PK
date_created    timestamp
data    json
schema_id   int(11)

数据如下所示:

[{"A": 1, "B": "Debra", "C": "Peters", "D": "dpeters0@accuweather.com", "E": "Female", "F": "Tsagaan-Ovoo"}, {"A": 2, "B": "Marc", "C": "Rasmussen", "D": "Marc@test.dk", "E": "Male", "F": "Copenhagen"}]

我正在尝试以下 sql 语句:

    SELECT
  data,
  JSON_EXTRACT(data, "$.F")        AS country,
  count(JSON_EXTRACT(data, "$.F")) AS num_residents
FROM kpi_data
WHERE schema_id = 39
GROUP BY country, data

但遗憾的是我得到了这个结果:

Answer 1

首先，您将所有 JSON 存储在一行中，如果您想像这样查询数据，每个居民都应该拥有自己的 JSON 行:

*other_fields*, {"A": 1, "B": "Debra", "C": "Peters", "D": "dpeters0@accuweather.com", "E": "Female", "F": "Tsagaan-Ovoo"}
*other_fields*, {"A": 2, "B": "Marc", "C": "Rasmussen", "D": "Marc@test.dk", "E": "Male", "F": "Copenhagen"}

其次，您不需要计算提取的数据，您只需要计算提取的每个国家/地区的行数。这将做到:

SELECT JSON_EXTRACT(data, "$.F") AS country,
       COUNT(*) AS num_residents

第三，您根本不想GROUP BY data，因为这可能是每个居民唯一的。这将为您留下每一行的一个组。我想你只是想:

GROUP BY country

综合起来:

  SELECT JSON_EXTRACT(data, "$.F") AS country,
         COUNT(*) AS num_residents
    FROM kpi_data
   WHERE schema_id = 39
GROUP BY country

为了获得不错的性能，您可以考虑在提取的国家/地区放置二级索引。请参阅 DOCS