时间戳

首页 > 解决方案 > 统计sql中的json标签

mysql - 统计sql中的json标签

问题描述

我有这个 json 字符串

[{"count": 9, "name": "fixkit", "label": "Repair Kit"}, {"count": 1, "name": "phone", "label": "Telefoon"}]
[{"count": 3, "name": "phone", "label": "Telefoon"}]
[{"count": 5, "name": "kunststof", "label": "Kunststof"}, {"count": 6, "name": "papier", "label": "Papier"}, {"count": 2, "name": "metaal", "label": "Metaal"}, {"count": 2, "name": "inkt", "label": "Inkt"}, {"count": 3, "name": "kabels", "label": "Kabels"}, {"count": 2, "name": "klei", "label": "Klei"}, {"count": 2, "name": "glas", "label": "Glas"}, {"count": 12, "name": "phone", "label": "Telefoon"}]
[{"count": 77, "name": "weed", "label": "Cannabis"}, {"count": 1, "name": "firework1", "label": "Vuurpijl 1"}]

并且知道我想要以下输出

Phone | Number of phones (in this case: 16)
Fixkit | Number of fixkits (in this case: 9)

我想用一个 sql 查询来做到这一点。如果您知道如何执行此操作，请提前致谢！

标签： mysqlsqlarraysjsonsum

解决方案

如果您正在运行 MySQL 8.0，您可以将数组取消嵌套到行中json_table()，然后过滤name您感兴趣的 s 并聚合。

假设您的表是mytable并且 json 列被称为js，那将是：

select j.name, sum(j.cnt) cnt
from mytable t
cross join json_table (
    t.js,
    '$[*]' columns(
        cnt int          path '$.count',
        name varchar(50) path '$.name'
    )
) j
where j.name in ('phone', 'fixkit')
group by j.name

DB Fiddle 上的演示：

| name   | cnt |
| ------ | --- |
| fixkit | 9   |
| phone  | 16  |

推荐阅读