N1QL聚合查询Couchbase

发布于 2025-01-12 03:46:15 字数 4384 浏览 6 评论 0原文

我正在尝试编写一个查询，该查询将聚合源和目标组合的一周中给定日期的文档结果。

存储桶中的文档如下所示

{
  "source": "test-source-1",
  "target": "test-target-1",
  "2022-03-05": {
    "day_of_week": "Saturday",
    "result-1": 467.5326086956522,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-06": {
    "day_of_week": "Sunday",
    "result-1": 467.5326086956522,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-12": {
    "day_of_week": "Saturday",
    "result-1": 467.5326086956522,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-13": {
    "day_of_week": "Sunday",
    "result-1": 190.8181818181818,
    "result-2": {
      "low_limit": 30.0,
      "high_limit": 30.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 6.0,
      "high_limit": 6.0
    }
  }
}

{
  "source": "test-source-2",
  "target": "test-target-2",
  "2022-03-05": {
    "day_of_week": "Saturday",
    "result-1": 300,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-06": {
    "day_of_week": "Sunday",
    "result-1": 400,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-12": {
    "day_of_week": "Saturday",
    "result-1": 300,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-13": {
    "day_of_week": "Sunday",
    "result-1": 400,
    "result-2": {
      "low_limit": 30.0,
      "high_limit": 30.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 6.0,
      "high_limit": 6.0
    }
  }
}

如果我们在所有文档中查询 where day_of_week = Saturday ，则预期结果应如下：

[
    {
        "2022-03-05": {
            "day_of_week": "Saturday",
            "result-1": 467.5326086956522,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          },
        "2022-03-12": {
            "day_of_week": "Saturday",
            "result-1": 467.5326086956522,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 1.0,
              "high_limit": 1.0
            },
            "result-4": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          }
        "source": "test-source-1",
        "target": "test-target-1"
    },
    {
        "2022-03-05": {
            "day_of_week": "Saturday",
            "result-1": 300,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          },
        "2022-03-12": {
            "day_of_week": "Saturday",
            "result-1": 300,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 1.0,
              "high_limit": 1.0
            },
            "result-4": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          },
        "source": "test-source-2",
        "target": "test-target-2"
    }
]

到目前为止，我有以下查询，但它返回所有 day_of_week。我知道我正在选择 b.* ，它将在所有日子中返回，只是不确定如何每天过滤掉它，即仅周六或周日

SELECT b.*
FROM `history-dummy`.`_default`.`node-to-node` AS b
WHERE ANY v IN OBJECT_VALUES(b) SATISFIES v.`day_of_week`="Saturday" END;

任何帮助将不胜感激。谢谢

原文

I'm trying to write a query that will aggregate results for documents for given day of week for a combination of source and target.

Documents in the bucket look like this

{
  "source": "test-source-1",
  "target": "test-target-1",
  "2022-03-05": {
    "day_of_week": "Saturday",
    "result-1": 467.5326086956522,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-06": {
    "day_of_week": "Sunday",
    "result-1": 467.5326086956522,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-12": {
    "day_of_week": "Saturday",
    "result-1": 467.5326086956522,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-13": {
    "day_of_week": "Sunday",
    "result-1": 190.8181818181818,
    "result-2": {
      "low_limit": 30.0,
      "high_limit": 30.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 6.0,
      "high_limit": 6.0
    }
  }
}

{
  "source": "test-source-2",
  "target": "test-target-2",
  "2022-03-05": {
    "day_of_week": "Saturday",
    "result-1": 300,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-06": {
    "day_of_week": "Sunday",
    "result-1": 400,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-12": {
    "day_of_week": "Saturday",
    "result-1": 300,
    "result-2": {
      "low_limit": 21.0,
      "high_limit": 14.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 3.0,
      "high_limit": 2.0
    }
  },
  "2022-03-13": {
    "day_of_week": "Sunday",
    "result-1": 400,
    "result-2": {
      "low_limit": 30.0,
      "high_limit": 30.0
    },
    "result-3": {
      "low_limit": 1.0,
      "high_limit": 1.0
    },
    "result-4": {
      "low_limit": 6.0,
      "high_limit": 6.0
    }
  }
}

And the expected result should be as follows if we are Querying for where day_of_week = Saturday in all documents:

[
    {
        "2022-03-05": {
            "day_of_week": "Saturday",
            "result-1": 467.5326086956522,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          },
        "2022-03-12": {
            "day_of_week": "Saturday",
            "result-1": 467.5326086956522,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 1.0,
              "high_limit": 1.0
            },
            "result-4": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          }
        "source": "test-source-1",
        "target": "test-target-1"
    },
    {
        "2022-03-05": {
            "day_of_week": "Saturday",
            "result-1": 300,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          },
        "2022-03-12": {
            "day_of_week": "Saturday",
            "result-1": 300,
            "result-2": {
              "low_limit": 21.0,
              "high_limit": 14.0
            },
            "result-3": {
              "low_limit": 1.0,
              "high_limit": 1.0
            },
            "result-4": {
              "low_limit": 3.0,
              "high_limit": 2.0
            }
          },
        "source": "test-source-2",
        "target": "test-target-2"
    }
]

I have the following query till now but it returns all the day_of_week.
I know I am selecting b.* which will return for all days just unsure how to filter this out for each day i.e only Saturday or Sunday

SELECT b.*
FROM `history-dummy`.`_default`.`node-to-node` AS b
WHERE ANY v IN OBJECT_VALUES(b) SATISFIES v.`day_of_week`="Saturday" END;

Any help would be greatly appreciated. Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倾城°AllureLove 2025-01-19 03:46:15

SELECT RAW OBJECT n:v FOR n:v IN b WHEN v.day_of_week = "Saturday" END
FROM `history-dummy`.`_default`.`node-to-node` AS b
WHERE ANY v IN OBJECT_VALUES(b) SATISFIES v.`day_of_week`="Saturday" END;

添加源、目标

SELECT b.source, b.target, OBJECT n:v FOR n:v IN b WHEN v.day_of_week = "Saturday" END.*
FROM `history-dummy`.`_default`.`node-to-node` AS b
WHERE ANY v IN OBJECT_VALUES(b) SATISFIES v.`day_of_week`="Saturday" END;

CREATE INDEX `idx1` ON `history-dummy`.`_default `.`node-to-node`(DISTINCT ARRAY v.day_of_week FOR v IN OBJECT_VALUES(self) END);

SELECT RAW OBJECT n:v FOR n:v IN b WHEN v.day_of_week = "Saturday" END
FROM `history-dummy`.`_default`.`node-to-node` AS b
WHERE ANY v IN OBJECT_VALUES(b) SATISFIES v.`day_of_week`="Saturday" END;

Add source, target

SELECT b.source, b.target, OBJECT n:v FOR n:v IN b WHEN v.day_of_week = "Saturday" END.*
FROM `history-dummy`.`_default`.`node-to-node` AS b
WHERE ANY v IN OBJECT_VALUES(b) SATISFIES v.`day_of_week`="Saturday" END;

CREATE INDEX `idx1` ON `history-dummy`.`_default `.`node-to-node`(DISTINCT ARRAY v.day_of_week FOR v IN OBJECT_VALUES(self) END);

回复收藏 0 原文

~没有更多了~