Couchbase N1QL 子选择结合 GROUP BY

debugcn 发表于 Dev

拉苏皮拉米

我有以下航班报价数据结构：

{
  "offerIdentifier": "123",
  "flyFrom": "HAM",
  "flyTo": "LGW",
  "provider": "LH",
  "price": 207,
  "bookingUrl": "https://example.com/",
  "flightCombinationIdentifier": "HAM-LGW-201791570-20179171835"
}

该flightCombinationIdentifier属性的值可以在上会出现多重优惠。

现在我想分组flightCombinationIdentifier并找到此组合可用的最低价格，理想情况下应该会产生这样的结构：

{
  "offerIdentifier": "456",
  "flightCombinationIdentifier": "HAM-LGW-201791570-20179171835",
  "offer": [
    {
      "bookingUrl": "http://example.com/",
      "price": 97,
      "provider": "LH"
    }
  ]
}

所以我想出了以下 N1QL 查询：

select b.flightCombinationIdentifier,
    (
        select b1.price, b1.provider, b1.bookingUrl from bucket b1
        use keys b.offerIdentifier
        where b1.flightCombinationIdentifier = b.flightCombinationIdentifier
        order by b1.price asc
        limit 1
    ) as offer

from bucket b
where b.flyFrom = 'HAM' and b.flyTo = 'LGW'
group by b.flightCombinationIdentifier

不幸的是，它因以下错误而死：

[
  {
    "code": 4210,
    "msg": "Expression must be a group key or aggregate: (select (`b1`.`price`), (`b1`.`provider`), (`b1`.`bookingUrl`) from `bucket` as `b1` use keys (`b`.`offerIdentifier`) where ((`b1`.`flightCombinationIdentifier`) = (`b`.`flightCombinationIdentifier`)))  order by (`b1`.`price`) limit 1) as `offer`",
    "query_from_user": "select b.flightCombinationIdentifier,\n    (\n        select b1.price, b1.provider, b1.bookingUrl from bucket b1\n        use keys b.offerIdentifier\n        where b1.flightCombinationIdentifier = b.flightCombinationIdentifier\n        order by b1.price asc\n        limit 1\n    ) as offer\n    \nfrom bucket w\nwhere b.flyFrom = 'HAM' and b.flyTo = 'LGW'\ngroup by b.flightCombinationIdentifier"
  }
]

将子查询的结果放入结果对象的正确方法是什么？

对决

SELECT flightCombinationIdentifier, MIN([price, {bookingUrl,price,provider}])[1] AS offer
FROM bucket WHERE flyFrom = 'HAM' AND flyTo = 'LGW'
GROUP BY flightCombinationIdentifier;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。