如何从表中JSON字段按条件提取值并计算元素时长差？

别担心，我来帮你搞定这个问题！你的需求是按element分组，计算每个元素的out时间减去in时间的差值，最终得到目标表格那样的结果对吧？下面我分几种常见场景给你具体的实现方法：

场景1：在数据库里处理JSON字段 #

如果你的JSON数据存在数据库中，不同数据库有不同的处理方式，这里给你讲最常用的两种：

PostgreSQL #

PostgreSQL对JSON的支持非常友好，用jsonb_array_elements可以轻松展开数组，然后分组计算：

SELECT
  elem->>'element' AS element,
  -- 提取out时间减去in时间
  (MAX(CASE WHEN elem->>'mention' = 'out' THEN (elem->>'time')::INT ELSE 0 END) -
   MAX(CASE WHEN elem->>'mention' = 'in' THEN (elem->>'time')::INT ELSE 0 END)) AS Timespent
FROM your_table,
     -- 把JSON数组拆成单独的行
     jsonb_array_elements(ui_data->'Ui') AS elem
GROUP BY elem->>'element';

MySQL 8.0+ #

MySQL 8.0及以上版本支持JSON_TABLE函数来解析数组，写法如下：

SELECT
  j.element,
  (MAX(CASE WHEN j.mention = 'out' THEN j.time ELSE 0 END) -
   MAX(CASE WHEN j.mention = 'in' THEN j.time ELSE 0 END)) AS Timespent
FROM your_table,
     JSON_TABLE(
       your_table.ui_data->'$.Ui',
       '$[*]' COLUMNS(
         element VARCHAR(50) PATH '$.element',
         mention VARCHAR(10) PATH '$.mention',
         time INT PATH '$.time'
       )
     ) AS j
GROUP BY j.element;

场景2：用Python处理JSON数据 #

如果是在代码里处理这个JSON字符串，用Python写几行代码就能搞定：

import json

# 你的原始JSON数据
json_str = '''{ "Ui": [ { "element": "TG1", "mention": "in", "time": 123 }, { "element": "TG1", "mention": "out", "time": 125 }, { "element": "TG2", "mention": "in", "time": 251 }, { "element": "TG2", "mention": "out", "time": 259 } ] }'''

# 解析JSON
data = json.loads(json_str)
# 用字典存储每个element的in和out时间
time_dict = {}

for item in data['Ui']:
    elem = item['element']
    # 如果是新的element，初始化字典
    if elem not in time_dict:
        time_dict[elem] = {'in': 0, 'out': 0}
    # 分别记录in和out的时间
    if item['mention'] == 'in':
        time_dict[elem]['in'] = item['time']
    elif item['mention'] == 'out':
        time_dict[elem]['out'] = item['time']

# 输出你想要的表格格式
print("| element | Timespent |")
print("|---------|-----------|")
for elem, times in time_dict.items():
    print(f"| {elem} | {times['out'] - times['in']} |")

运行这段代码后，就能直接得到你想要的结果：

核心思路总结 #

不管用哪种方法，核心步骤都是这三步：

1. 拆分数组：把JSON数组里的每个对象单独拿出来，变成一条条的记录
2. 分组归类：把同一个element的in和out时间放到一起
3. 计算差值：用out的时间减去in的时间，得到最终的停留时长

这样就能完美得到你需要的结果啦～

内容的提问来源于stack exchange，提问作者Srinivasan Iyer