EMQ 问答社区

EMQX sql规则使用问题

环境信息

  • EMQ X 版本:4.3.2
  • 操作系统及版本:ubuntu20.04
  • 其他

问题描述

按照文档中 遍历语法(FOREACH-DO-INCASE) 举例 规则引擎 | EMQ Docs

  1. 使用中遇到疑问:请教编写能实现需求的sql规则(把原始数据中date 项,在数组项中体现)
    原始数据:
{
    "date": "2020-04-24",
    "sensors": [
        {"name": "a", "idx":0},
        {"name": "b", "idx":1},
        {"name": "c", "idx":2}
    ]
}

经过规则 foreach 遍历的数组 sensors,

FOREACH
    payload.sensors as s
DO
    clientid,
    s.name as name,
    s.idx as idx
INCASE
    s.idx >= 1
FROM "t/#"

则选取结果为:

[
  {
    "name": "b",
    "idx": 1,
    "clientid": "c_emqx"
  },
  {
    "name": "c",
    "idx": 2,
    "clientid": "c_emqx"
  }
]

请教:想要经过规则转换后的结果中把 date 项加入到每个数组项中,要如何编写规则,尝试了一些写法都无法达到
想要达到的效果为:

[
  {
    "date":  "2020-04-24",
    "name": "b",
    "idx": 1,
    "clientid": "c_emqx"
  },
  {
    "date":  "2020-04-24",
    "name": "c",
    "idx": 2,
    "clientid": "c_emqx"
  }
]

能否通过编写sql实现上述结果的输出?

FOREACH
    payload.sensors as s
DO
    clientid,
    s.name as name,
    s.idx as idx,
    payload.date as date
INCASE
    s.idx >= 1
FROM "t/#"
1赞