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?

Reading the blog post about [federated querying](https://cloudant.com/blog/technical-look-at-cloudant-search/#.VN4LafnF_A4), I guess this should work. — Maarten, Feb 13 '15 at 14:59

score 1 · Accepted Answer · edited May 23 '17 at 12:27

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?

Cloudant: Searching across databases

1 Answers1

Linked