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 kp711@yahoo.com
but couldn't succeed.
Couchbase docs
[
{
"type": "customer",
"customer_id": <UUID4>,
"user_type": "owner",
"first_name": "",
"last_name": "",
"email": "kp711@yahoo.com",
"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": "px800@yahoo.com",
"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?
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;