mysql - 如何在 MySQL 8 中提取和展平 JSON 对象内部的数组?
问题描述
给定一些像这样的 json 文档:
{
"_id":"00006073",
"subscribersIds":[
170968,
225647
]
}
-----------------------------------
{
"_id":"00006072",
"subscribersIds":[
170968
]
}
--------------------------------
{
"_id":"00006074,
"subscribersIds":[
228195,
225647
]
}
你知道我怎样才能得到一个subscribersIds的列表,而不用重复吗?结果应该是这样的170968, 225647, 228195
,因为我需要将此查询的结果用作另一个查询的条件。
对于 Couchebase 有执行此操作的“UNNEST”命令,但是我没有找到在 MySQL 8 中执行此操作的正确方法,如SELECT DISTINCT doc ->> '$.subscribersIds[*]' FROM customers
将返回[170968,225647],[170968],[228195,225647]
提前致谢!
解决方案
mysql> select * from mytable;
+----------+------------------+
| _id | subscriberIds |
+----------+------------------+
| 00006072 | [170968] |
| 00006073 | [170968, 225647] |
| 00006074 | [228195, 225647] |
+----------+------------------+
mysql> select j.subscriberId from mytable,
json_table(mytable.subscriberIds, '$[*]' columns (subscriberId int path '$')) j;
+--------------+
| subscriberId |
+--------------+
| 170968 |
| 170968 |
| 225647 |
| 228195 |
| 225647 |
+--------------+
mysql> select distinct j.subscriberId from mytable,
json_table(mytable.subscriberIds, '$[*]' columns (subscriberId int path '$')) j;
+--------------+
| subscriberId |
+--------------+
| 170968 |
| 225647 |
| 228195 |
+--------------+
这是一个相当复杂的查询,每次您想要获取一组不同的subscriberId 时都要编写。
如果您根本不使用 JSON 会容易得多,而是以标准化方式存储 id,在第二个表中每行一个。
mysql> create table mySubscribers (_id char(8), subscriberId int, primary key (_id, subscriberId));
mysql> insert into mySubscribers (_id, subscriberId) select _id, subscriberId from mytable, json_table(subscriberIds, '$[*]' columns (subscriberId int path '$')) j;
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from mySubscribers;
+----------+--------------+
| _id | subscriberId |
+----------+--------------+
| 00006072 | 170968 |
| 00006073 | 170968 |
| 00006073 | 225647 |
| 00006074 | 225647 |
| 00006074 | 228195 |
+----------+--------------+
mysql> select distinct subscriberId from mySubscribers;
+--------------+
| subscriberId |
+--------------+
| 170968 |
| 225647 |
| 228195 |
+--------------+
推荐阅读
- java - 编写程序接收10-12长类型的数据
- http - Tomcat 偶尔会返回没有 HTTP 标头的响应
- javascript - 任何人都可以检查为什么附加的代码不起作用
- python - 如何在右侧 cilck 上的 Qdoublespinbox 上向 QtCore.Qt.DefaultContextMenu 添加操作?
- java - 条纹:添加默认卡并且不覆盖旧卡
- c# - 域用户选择器窗口控件?
- scorm - 您可以通过 SCORM 课程调用外部 api
- c++ - QDockWidget 中的 QToolBox 折叠到最小尺寸
- sapui5 - 输入辅助不显示所有建议
- node.js - 如何将旧代码库中的“bcrypt-as-promised”安装到我的 ubuntu 机器上?