Query with Dynamic field values of Couchbase

Question

With the below structure of Couchbase bucket, how do I query if the nested doc has dynamic field name?

Here, I would like to return the customer docs who have account in Hyderabad

I tried to query this way but couldn't succeed.

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

I was expecting to return the customer doc with email [email protected] but couldn't succeed.

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

Is there a way in Couchbase to fetch in this way?

Answer 1

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

OR

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