库基N1QL子选择与组组合

Question

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

{
  "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"
  }
]

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

Answer 1

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