Cloudant: Searching across databases

Question

I have documents across 2 different databases: fruits, vegetables. It's easier for me to keep the databases separated.

Now, suppose I want my user to search from any combination of these databases. Would it work if I run the same query across the three databases, and merge the result. That is: does the order field in a results have an absolute value, or is it relative to the other results? For example:

Run my query on fruits db:

{
  total_rows: 2,
  bookmark: "xxx",
rows: [
{
  id: "Apple",
  order: [
    2,
    220
  ],
  fields: {
    title: "Apple"
  }
},
{
  id: "pear",
  order: [
    1,
    4223
  ],
  fields: {
    title: "Pear"
  }
}
}

Run my query on vegetable database:

{
  total_rows: 1,
  bookmark: "xxx",
rows: [
{
  id: "brocolli",
  order: [
    1.5,
    3000
  ],
  fields: {
    title: "Brocolli"
  }
}
}

Then bringing the results together to produce

{
  total_rows: 2,
  bookmark: "xxx",
rows: [
{
  id: "Apple",
  order: [
    2,
    220
  ],
  fields: {
    title: "Apple"
  }
},
{
  id: "brocolli",
  order: [
    1.5,
    3000
  ],
  fields: {
    title: "Brocolli"
  }
},
{
  id: "pear",
  order: [
    1,
    4223
  ],
  fields: {
    title: "Pear"
  }
}
}

Would this work? Or is it better to just make a single foods database?

Counterproductive

Reading the blog post about federated querying, I guess this should work. – Counterproductive

Answer 1

It is not possible to perform joins across databases using CouchDB or Cloudant. You will need to either:

• put all your data in a single database and query that
• have separate databases and replicate the data from each to a single database and query that
• have separate databases and perform the join functionality in your application tier

I've added this question to: How can I use my sql knowledge with Cloudant/CouchDB?

Sola