# Using result of one subquery in another subquery
**URL:** https://discuss.elastic.co/t/using-result-of-one-subquery-in-another-subquery/7241
**Category:** Elasticsearch
**Created:** [April 5, 2012, 11:05am UTC](https://discuss.elastic.co/t/using-result-of-one-subquery-in-another-subquery/7241 "2012-04-05T11:05:37Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@bgossey](https://discuss.elastic.co/u/bgossey)
#### Post date: [April 5, 2012, 11:05am UTC](https://discuss.elastic.co/t/using-result-of-one-subquery-in-another-subquery/7241/1 "2012-04-05T11:05:37Z")
Hey,
Is the following query possible with elastic search:
I have two types: company and category
- Company is a type containing fields like name, phone, keywords,
location… and also a special field called category-id which contains
an id (number)
company {
name:truvo,
phone:(0032)123.32.42
keywords: yellow pages, white pages
…
category-id: 1234
}
- Category is a type containing an id (the category id mentioned
above) and a set of names for that category
category {
category-id:1234
names: software, applications
}
Now I want to do a query: give me all companies where the category is
software and it should return the list of companies in this category
(so it would include Truvo). ~ a join in sql
Of course I can merge the two types before indexing, but keeping it in
two separate types provides me the flexibility to update the listings
(for example adding a synonym computer programs to the category 1234)
without the need to reindex the companies.
I could of course do this also by doing two queries (the first one to
retrieve the categories, because it could be more than one category)
and then doing a second query using the result of the first query, but
I would like (id possible) to do this in one query.
Any suggestions?
thanks,
Bert
---
### Author:  [@kimchy](https://discuss.elastic.co/u/kimchy)
#### Post date: [April 8, 2012, 5:58pm UTC](https://discuss.elastic.co/t/using-result-of-one-subquery-in-another-subquery/7241/2 "2012-04-08T17:58:01Z")
Both options you mentioned are sound. The other option is possibly to use
parent child feature, but note that a in parent child relationship is one
(parent) to many (children).
On Thu, Apr 5, 2012 at 2:05 PM, bert gossey [bgossey@hotmail.com](mailto:bgossey@hotmail.com) wrote:
> Hey,
>
> Is the following query possible with Elasticsearch:
>
> I have two types: company and category
>
> - Company is a type containing fields like name, phone, keywords,
> location… and also a special field called category-id which contains
> an id (number)
>
> company {
> name:truvo,
> phone:(0032)123.32.42
> keywords: yellow pages, white pages
> …
> category-id: 1234
> }
>
> - Category is a type containing an id (the category id mentioned
> above) and a set of names for that category
>
> category {
> category-id:1234
> names: software, applications
> }
>
> Now I want to do a query: give me all companies where the category is
> software and it should return the list of companies in this category
> (so it would include Truvo). ~ a join in sql
>
> Of course I can merge the two types before indexing, but keeping it in
> two separate types provides me the flexibility to update the listings
> (for example adding a synonym computer programs to the category 1234)
> without the need to reindex the companies.
>
> I could of course do this also by doing two queries (the first one to
> retrieve the categories, because it could be more than one category)
> and then doing a second query using the result of the first query, but
> I would like (id possible) to do this in one query.
>
> Any suggestions?
>
> thanks,
> Bert
---
### Author:  [@Ray\_Ward\_2](https://discuss.elastic.co/u/Ray_Ward_2)
#### Post date: [October 15, 2012, 3:04am UTC](https://discuss.elastic.co/t/using-result-of-one-subquery-in-another-subquery/7241/3 "2012-10-15T03:04:14Z")
Hi kimchy,
Could you give an example of how to perform this all in one query? Or is
that not actually possibly?
Thanks
On Monday, April 9, 2012 2:58:01 AM UTC+9, kimchy wrote:
> Both options you mentioned are sound. The other option is possibly to use
> parent child feature, but note that a in parent child relationship is one
> (parent) to many (children).
>
> On Thu, Apr 5, 2012 at 2:05 PM, bert gossey \<[bgo...@hotmail.com](mailto:bgo...@hotmail.com)\
>
> > wrote:
>
> > Hey,
> >
> > Is the following query possible with Elasticsearch:
> >
> > I have two types: company and category
> >
> > - Company is a type containing fields like name, phone, keywords,
> > location… and also a special field called category-id which contains
> > an id (number)
> >
> > company {
> > name:truvo,
> > phone:(0032)123.32.42
> > keywords: yellow pages, white pages
> > …
> > category-id: 1234
> > }
> >
> > - Category is a type containing an id (the category id mentioned
> > above) and a set of names for that category
> >
> > category {
> > category-id:1234
> > names: software, applications
> > }
> >
> > Now I want to do a query: give me all companies where the category is
> > software and it should return the list of companies in this category
> > (so it would include Truvo). ~ a join in sql
> >
> > Of course I can merge the two types before indexing, but keeping it in
> > two separate types provides me the flexibility to update the listings
> > (for example adding a synonym computer programs to the category 1234)
> > without the need to reindex the companies.
> >
> > I could of course do this also by doing two queries (the first one to
> > retrieve the categories, because it could be more than one category)
> > and then doing a second query using the result of the first query, but
> > I would like (id possible) to do this in one query.
> >
> > Any suggestions?
> >
> > thanks,
> > Bert
--
---
### Author:  [@jprante](https://discuss.elastic.co/u/jprante)
#### Post date: [October 15, 2012, 7:16am UTC](https://discuss.elastic.co/t/using-result-of-one-subquery-in-another-subquery/7241/4 "2012-10-15T07:16:31Z")
Hi Ray,
have you thought of dropping "category-id" and just use a filter query on
the company index looking for the "category" as a string?
{
"company": {
"name" : "truvo",
"category" : ["software", "applications", ...]
}
}
This process is called denormalization. Data fields like "category-id" are
useful for relational queries, but are "poison" for efficient document
retrieval.
For parent/child approach, you have to index categories as parents, and
hook companies to a single category (which is obviuosly not what you asked
for). Think of parent/child as building a tree, not a table.
Jörg
On Monday, October 15, 2012 5:04:14 AM UTC+2, Ray Ward wrote:
> Hi kimchy,
>
> Could you give an example of how to perform this all in one query? Or is
> that not actually possibly?
>
> Thanks
>
> On Monday, April 9, 2012 2:58:01 AM UTC+9, kimchy wrote:
>
> > Both options you mentioned are sound. The other option is possibly to use
> > parent child feature, but note that a in parent child relationship is one
> > (parent) to many (children).
> >
> > On Thu, Apr 5, 2012 at 2:05 PM, bert gossey [bgo...@hotmail.com](mailto:bgo...@hotmail.com) wrote:
> >
> > > Hey,
> > >
> > > Is the following query possible with Elasticsearch:
> > >
> > > I have two types: company and category
> > >
> > > - Company is a type containing fields like name, phone, keywords,
> > > location… and also a special field called category-id which contains
> > > an id (number)
> > >
> > > company {
> > > name:truvo,
> > > phone:(0032)123.32.42
> > > keywords: yellow pages, white pages
> > > …
> > > category-id: 1234
> > > }
> > >
> > > - Category is a type containing an id (the category id mentioned
> > > above) and a set of names for that category
> > >
> > > category {
> > > category-id:1234
> > > names: software, applications
> > > }
> > >
> > > Now I want to do a query: give me all companies where the category is
> > > software and it should return the list of companies in this category
> > > (so it would include Truvo). ~ a join in sql
> > >
> > > Of course I can merge the two types before indexing, but keeping it in
> > > two separate types provides me the flexibility to update the listings
> > > (for example adding a synonym computer programs to the category 1234)
> > > without the need to reindex the companies.
> > >
> > > I could of course do this also by doing two queries (the first one to
> > > retrieve the categories, because it could be more than one category)
> > > and then doing a second query using the result of the first query, but
> > > I would like (id possible) to do this in one query.
> > >
> > > Any suggestions?
> > >
> > > thanks,
> > > Bert
--
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 3:08am UTC](https://discuss.elastic.co/t/using-result-of-one-subquery-in-another-subquery/7241/5 "2017-07-06T03:08:53Z")