Selector query comparing two fields Cloudant/CouchDB/Mango

Question

I have a CouchDB database, which uses a query language Mango - which seems to be the same as Cloudant's query language.

I'm trying to search and compare two fields to each other and only return the relevant results when they're equal.

For example:

{
 "_id": "ACCEPT0",
 "_rev": "1-92ea4e727271aefd0a2befed0d4bb736",
 "OfferID": "OFFER0"
}

{
 "_id": "ACCEPT1",
 "_rev": "3-986ca6e717b225ac909d644de54d5f7d",
 "OfferID": "OFFER3"
}

{
 "_id": "OFFER0",
 "_rev": "1-2af5f5c7b1c59dd3f0997f748a367cb2",
 "From": "merchant1",
 "To": "customer1"
}

{
 "_id": "OFFER1",
 "_rev": "6-f0927c5d4f9fd8a2d2b602f1c265d6d5",
 "From": "merchant1",
 "To": "customer2"
}

I trying to come up with a query which will, in this example, return "OFFER0" - since OFFER0 exists in an "OfferID"

EDIT (clarification): The query needs to be able to select all the _id's which begin with OFFER and which exist in an OfferID field.

I know I can set this up with a view (as seen from: Cloudant query to return records where 2 fields are equal), but I need this in a Mango query as it'll be running over Hyperledger

score 3 · Answer 1 · answered Aug 30 '17 at 13:17

You can easily return documents whose _id field starts with "OFFER" with the following query:

{
  "selector": {
    "_id": {
      "$regex": "^OFFER"
    }
  }
}

but this is likely to be inefficient because Cloudant has to scan the whole database, testing each documents _id field with that regular expression.

A better way to design your data may be to have a type field which distinguishes between the document types in your database e.g.

{
 "_id": "OFFER0",
 "_rev": "1-2af5f5c7b1c59dd3f0997f748a367cb2",
 "type": "offer",
 "From": "merchant1",
 "To": "customer1"
}

and then a query to return all documents where type = 'offer' becomes:

{
  "selector": {
    "type": "offer"
  }
}

I don't fully understand the part of the question where you say "which exist in an OfferID field." but it's important to note that Cloudant Query & Mango can only query single documents - you can't say "get me all the documents which are offers, where another document has a certain property". Include all the data you need in each document and then you'll be able to query it cleanly.

Selector query comparing two fields Cloudant/CouchDB/Mango

1 Answers1