CODEWITHSUNDEEP
couchbasesql++couchbase-lite
N Raghu
N Raghu

Reputation: 746

Query with Dynamic field values of Couchbase

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?

Upvotes: 2

Views: 329

Answers (2)

vsr
vsr

Reputation: 7414

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;

Upvotes: 3

preale
preale

Reputation: 21

The answer from VSR provides a neat solution to the problem. Remember also that you will want to provide an index in order to leverage the Couchbase query services optimally. I'm guessing (based on your doc examples) that you will have multiple document types included in your bucket. With that in mind, here is an example index create statement:

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

Now you can use it as part of your WHERE clause:

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

Upvotes: 2

Related Questions