与Couchbase的动态场值查询

Question

使用Couchbase Bucket的以下结构，如何查询嵌套DOC是否具有动态字段名称？

在这里，我想返回在hyderabad中使用帐户的客户文档，

我试图以这种方式查询但无法成功。

select * from bucket where accounts.$.city = 'Hyderabad'

我期望通过电子邮件，但无法成功。

Couchbase Docs

[
{
  "type": "customer",
  "customer_id": <UUID4>,
  "user_type": "owner",
  "first_name": "",
  "last_name": "",
  "email": "[email protected]",
  "password": "",
  "phone_number": 11111,
  "accounts": {
    <account_id which is UUID4>: {
      "amount": "500",
      "city": "Hyderabad"
    }
  }
},
{
    "type": "customer",
    "customer_id": <UUID4>,
    "user_type": "employee",
    "first_name": "",
    "last_name": "",
    "email": "[email protected]",
    "password": "",
    "phone_number": 33333,
    "accounts": {
      <account_id which is UUID4>: {
        "amount": "500",
        "city": "Chennai"
      }
    }
  }
]

在Couchbase中是否有办法以这种方式获取？

Answer 1

SELECT b.*
FROM bucket AS b
WHERE ANY v IN OBJECT_VALUES(b.accounts) SATISFIES v.city = 'Hyderabad' END;

或者

SELECT b.*
FROM bucket AS b
WHERE ANY n:v IN b.accounts SATISFIES v.city = 'Hyderabad' END;

Answer 2

VSR的答案为问题提供了整洁的解决方案。还请记住，您将需要提供索引，以便最佳地利用Couchbase查询服务。我猜（根据您的文档示例），您的存储桶中包含多种文档类型。考虑到这一点，这是一个示例索引创建语句：

create index idxTypeCustomer on bucket(type) where type = 'customer';

现在您可以将其用作WHERE子句的一部分：

SELECT b.*
FROM bucket AS b
WHERE type = 'customer'
  AND ANY v IN OBJECT_VALUES(b.accounts) SATISFIES v.city = 'Hyderabad' END;