代码之家 › 专栏 › 技术社区 › meallhour

如何在MySQL中使用JSON文档连接子查询?

inner-join join json sql mysql

1

meallhour · 技术社区 · 5 年前

Mysql版本是 8.0.18-commercial .该表的主键是 id . 我编写了以下查询,显示 hostname 和 details 柱

select hostname, details from table t1;

hostname: abc123
details:
[
  {
    "Msg": "Job Running",
    "currentTask": "IN_PROGRESS",
    "activityDate": "2020-07-20 16:25:15"
  },
  {
    "Msg": "Job failed",
    "currentTask": "IN_PROGRESS",
    "activityDate": "2020-07-20 16:35:24"
  }
]

我想要 Msg 仅来自具有最新值的元素的值 activityDate

我想要的输出正在显示 主机名 与 消息 元素与 latest date :

hostname        Msg
abc123          Job failed

我编写了以下查询,它正在成功运行,但根本没有显示任何内容。更重要的是,这需要 17secs 执行。

select hostname,
(select Msg
from (
    select x.*, row_number() over(partition by t.id order by x.activityDate) rn
    from table1 t
    cross join json_table(
        t.audits,
        '$[*]' columns(
            Msg varchar(50) path '$.Msg',
            activityDate datetime path '$.activityDate'
        )
    ) x
) t
where rn = 1) AS Msg
from table1;

0 回复 | 直到 5 年前

1

2

Barbaros Özhan 5 年前

您需要通过删除末尾的逗号来修复JSON的格式以开头的行 "activityDate" 钥匙
转换函数,例如 STR_TO_DATE() 应适用于衍生 activityDate 列,以便按顺序排列日期(不是 个性方面 )结果。
通过put不需要子查询 ROW_NUMBER() 分析函数旁边 ORDER BY 条款( 具有下降秩序 ),并添加a LIMIT 1 条款在查询结束时

因此,您可以将查询重写为

SELECT t1.hostname,
       j.Msg
  FROM t1
 CROSS JOIN
     JSON_TABLE(details, '$[*]' 
       COLUMNS (

                Msg VARCHAR(100)  PATH '$.Msg',
                activityDate VARCHAR(100)  PATH '$.activityDate'                        
               )
     ) j 
 ORDER BY ROW_NUMBER() 
          OVER ( -- PARTITION BY id 
                ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC)    
 LIMIT 1

Demo

更新 :

对于有多个id值的情况,您可以考虑使用 行数() 在子查询中执行函数,并过滤掉主查询中返回等于1的值:

SELECT id, Msg
  FROM
  (
   SELECT t1.*, j.Msg,
          ROW_NUMBER() 
          OVER (PARTITION BY id 
                ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC) AS rn   
     FROM t1
    CROSS JOIN
          JSON_TABLE(details, '$[*]' 
          COLUMNS (    
                   Msg VARCHAR(100)  PATH '$.Msg',
                   activityDate VARCHAR(100)  PATH '$.activityDate'                        
                  )
     ) j 
   ) q
 WHERE rn= 1

Demo

另一种方法使用 行数() 功能与 LIMIT 子句包含Correlated Subquery,适用于具有多个 身份证件 值:

SELECT t.id, 
 ( SELECT j.Msg
     FROM t1
    CROSS JOIN
        JSON_TABLE(details, '$[*]' 
        COLUMNS (
                Msg VARCHAR(100)  PATH '$.Msg',
                activityDate VARCHAR(100)  PATH '$.activityDate'                        
                )
        ) j
     WHERE t1.id = t.id  
     ORDER BY ROW_NUMBER() 
              OVER (ORDER BY STR_TO_DATE(j.activityDate, '%Y-%m-%d %H:%i:%S') DESC)    
     LIMIT 1 ) AS Msg
  FROM t1 AS t

Demo

2

1

yg-dba 5 年前

也许我是老派,但日期字段应该作为单独的字段存储在JSON之外,以便于查询。

ID是否自动递增,数据是否按时间戳顺序插入?如果是,那么您可以运行这样的查询,为每个主机名提供最后一行:

SELECT id, hostname, details 
FROM table t1
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.hostname = t1.hostname AND t2.id > t1.id) ;