Couchbase N1QL阵列查询 [英] Couchbase N1QL array query

问题描述

我的 giata_properties 存储桶中的文档样本：链接

Document sample from my giata_properties bucket: link

相关的json粘贴

{
  "propertyCodes": {
    "provider": [
      {
        "code": [
          {
            "value": [
              {
                "value": "304387"
              }
            ]
          }
        ],
        "providerCode": "hotelbeds",
        "providerType": "gds"
      },
      {
        "code": [
          {
            "value": [
              {
                "name": "Country Code",
                "value": "EG"
              },
              {
                "name": "City Code",
                "value": "HRG"
              },
              {
                "name": "Hotel Code",
                "value": "91U"
              }
            ]
          }
        ],
        "providerCode": "gta",
        "providerType": "gds"
      }
    ]
  },
  "name": "Arabia Azur Resort"
}

我要一个查询（和索引）以基于 propertyCodes.provider.code.value.value 和 propertyCodes.provider.providerCode 。我已经设法分别进行了处理，但是我不确定如何在单个查询中将它们合并。

I want a query (and an index) to retrieve a document based on propertyCodes.provider.code.value.value and propertyCodes.provider.providerCode. I've managed to do each separately but I'm not sure how to merge both of them in a single query.

SELECT meta().id FROM giata_properties AS gp USE INDEX(`#primary`) WHERE ANY v WITHIN gp.propertyCodes.provider[*].code SATISFIES v.`value` = '150613' END;

SELECT meta().id FROM giata_properties AS gp USE INDEX(`#primary`) WHERE ANY v within gp.propertyCodes.provider[*].providerCode SATISFIES v = 'hotelbeds' END;

例如，我想获取包含 propertyCodes.provider的文档。 304387 的code.value.value 和 provider 的 hotelbeds ，因为代码值可以在文档上重复，但是代码和providerCode的组合是唯一的。

So for example I want to fetch the document that includes propertyCodes.provider.code.value.value of 304387 and that provider is also hotelbeds, because code value can be duplicated over documents, but code and providerCode combination is unique.

推荐答案

这里是查询和索引。

查询。

SELECT META().id
FROM giata_properties AS gp
WHERE ANY p IN propertyCodes.provider SATISFIES ( ANY v WITHIN p.code SATISFIES v.`value` = '304387' END ) AND p.providerCode = 'hotelbeds' END;

索引。

CREATE INDEX idx_value ON giata_properties
( DISTINCT ARRAY ( DISTINCT ARRAY v.`value` FOR v WITHIN p.code END ) FOR p IN propertyCodes.provider END );

CREATE INDEX idx_providerCode ON giata_properties
( DISTINCT ARRAY p.providerCode FOR p IN propertyCodes.provider END );

这篇关于Couchbase N1QL阵列查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！