MySQL 8 如何从表存储的JSON数组中提取符合指定条件的JSON对象

你存储的原始JSON数组结构如下：

[
  "{\"delivered\":0,\"deliveryDate\":1633537686480,\"toUserId\":148}",
  "{\"delivered\":0,\"deliveryDate\":1633537687590,\"toUserId\":226}",
  "{\"delivered\":1,\"deliveryDate\":1633537687741,\"toUserId\":160}",
  "{\"delivered\":0,\"deliveryDate\":1633537687863,\"toUserId\":262}",
  "{\"delivered\":0,\"deliveryDate\":1633537688019,\"toUserId\":263}",
  "{\"delivered\":0,\"deliveryDate\":1633537688174,\"toUserId\":264}",
  "{\"delivered\":0,\"deliveryDate\":1633537688325,\"toUserId\":265}"
]

需求为按指定toUserId和delivered字段值筛选匹配的JSON对象，可通过以下两类方案实现：

方案1：数据库层面直接查询（以MySQL为例） #

适用场景 #

• 不需要额外业务处理，直接从数据库返回结果
• MySQL版本>=8.0（支持JSON_TABLE函数，查询逻辑更简洁）

实现代码 #

假设表名为delivery_records，存储JSON数组的字段名为delivery_info，查询toUserId=148、delivered=0匹配项的代码如下：

SELECT matched_item
FROM delivery_records,
JSON_TABLE(
  -- 将数组中字符串类型的JSON元素转为结构化JSON对象
  JSON_TRANSFORM(delivery_info, '$[*]': JSON),
  '$[*]' COLUMNS (
    matched_item JSON PATH '$',
    toUserId INT PATH '$.toUserId',
    delivered INT PATH '$.delivered'
  )
) AS jt
WHERE jt.toUserId = 148 AND jt.delivered = 0;

方案2：应用代码层面筛选（以Python为例） #

适用场景 #

• 数据量较大，SQL JSON函数性能不满足需求
• 需要对数据做额外业务处理，对可维护性要求更高

实现逻辑 #

1. 从数据库查询取出原始JSON数组
2. 解析JSON结构，若数组元素为字符串格式则二次转为JSON对象
3. 遍历数组匹配toUserId和delivered字段，返回匹配结果

实现代码 #

import json
import mysql.connector

# 1. 数据库查询取数
db_conn = mysql.connector.connect(host="数据库地址", user="用户名", password="密码", database="库名")
cursor = db_conn.cursor()
cursor.execute("SELECT delivery_info FROM delivery_records WHERE 你的查询条件")
raw_json = cursor.fetchone()[0]

# 2. 解析JSON结构
delivery_list = json.loads(raw_json)
# 二次转换字符串类型的JSON元素为字典
delivery_list = [json.loads(item) if isinstance(item, str) else item for item in delivery_list]

# 3. 筛选匹配项
target_to_user = 148
target_status = 0
result = None
for item in delivery_list:
    if item["toUserId"] == target_to_user and item["delivered"] == target_status:
        result = item
        break

print(result)
# 输出：{'delivered': 0, 'deliveryDate': 1633537686480, 'toUserId': 148}

注意事项 #

• 若数据库存储的JSON数组本身就是结构化的JSON对象（无额外字符串转义），可省略二次转义的步骤
• 生产环境建议优先选用应用代码层面处理的方案，性能和可维护性更高

内容的提问来源于stack exchange，提问作者SYED MUSTAFA HUSSAIN