# Search by child product when search by sku in elastic search query **URL:** \ **Category:** Kibana\ **Created:** [August 30, 2022, 1:23pm UTC](https://discuss.elastic.co/t/search-by-child-product-when-search-by-sku-in-elastic-search-query/313256 "2022-08-30T13:23:05Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![Sangeet\_Shah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sangeet_shah/32/106319_2.png) [@Sangeet\_Shah](https://discuss.elastic.co/u/Sangeet_Shah)\ **Post date:** [August 30, 2022, 1:23pm UTC](https://discuss.elastic.co/t/search-by-child-product-when-search-by-sku-in-elastic-search-query/313256/1 "2022-08-30T13:23:05Z")
I have some products below ```auto ``` Id Name SKU isParent 1 A 100 True 2 A1 101 False 3 A2 102 False 4 A3 103 False 5 A4 104 False 6 B 105 True 7 B1 106 False 8 B2 107 False I have query to find the product by name,SKU. I want to exclude the product which is IsParent is false when try to search by name but when its exact search by sku then all product need to be search. How to do it? Search by name is working fine with excluding isParent false products. My elastic query as below ```auto { "min_score": 810, "query": { "bool": { "must": [ { "term": { "Published": { "value": "true" } } }, { "terms": { "Stores.Id": [ 0, 1 ] } }, { "bool": { "must_not": { "term": { "IsParentProduct": "false" } } } }, { "bool": { "should": [ { "match_phrase": { "Name": { "query": "BGEBASIC33", "boost": 100 } } }, { "match": { "Name": { "query": "BGEBASIC33", "boost": 150 } } }, { "match_phrase": { "ProductCategories.Name.exact": { "query": "BGEBASIC33", "boost": 200 } } }, { "match": { "ProductCategories.Name.exact": { "query": "BGEBASIC33", "boost": 300 } } }, { "match_phrase": { "ParentCategory.Name": { "query": "BGEBASIC33", "boost": 1000 } } }, { "match": { "ParentCategory.Name": { "query": "BGEBASIC33", "boost": 750 } } }, { "match_phrase": { "ShortDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match": { "ShortDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match_phrase": { "FullDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match": { "FullDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match_phrase": { "ProductManufacturer.Name.exact": { "query": "BGEBASIC33", "boost": 200 } } }, { "match": { "ProductManufacturer.Name.exact": { "query": "BGEBASIC33", "boost": 200 } } }, { "nested": { "path": "ProductPromoTags", "query": { "bool": { "must": [ { "bool": { "should": [ { "match_phrase": { "ProductPromoTags.Name": { "query": "BGEBASIC33", "boost": 100 } } }, { "match": { "ProductPromoTags.Name": { "query": "BGEBASIC33", "boost": 100 } } } ] } }, { "bool": { "should": [ { "match_phrase": { "ProductPromoTags.SystemName": { "query": "BGEBASIC33", "boost": 100 } } }, { "match": { "ProductPromoTags.SystemName": { "query": "BGEBASIC33", "boost": 100 } } } ] } } ] } } } }, { "match": { "Sku": { "query": "BGEBASIC33", "boost": 500 } } }, { "match_phrase": { "Sku": { "query": "BGEBASIC33", "boost": 100 } } }, { "nested": { "path": "SpecificationAttributes", "query": { "bool": { "should": [ { "match_phrase": { "SpecificationAttributes.SpecificationAttributeOptions.Name.exact": { "query": "BGEBASIC33", "boost": 80 } } }, { "match": { "SpecificationAttributes.SpecificationAttributeOptions.Name.exact": { "query": "BGEBASIC33", "boost": 100 } } } ] } } } } ] } } ] } }, "from": 0, "size": 32, "aggs": { "PriceRanges": { "range": { "field": "FinalPrice", "ranges": [ { "from": "0", "to": "100" }, { "from": "101", "to": "998" }, { "from": "999", "to": "1999" }, { "from": "2000", "to": "2999" }, { "from": "3000" } ] } }, "ProductCategoryCustomURL": { "terms": { "field": "ProductCategories.CustomURL", "size": 100 } }, "ProductManufacturer": { "terms": { "field": "ProductManufacturer.Name", "size": 100 } }, "SpecificationAttributesfiltered": { "filter": { "bool": { "must": [ ] } }, "aggs": { "SpecificationAttributes": { "nested": { "path": "SpecificationAttributes" }, "aggs": { "Spec_group": { "terms": { "field": "SpecificationAttributes.Name", "size": 100 }, "aggs": { "Opctions": { "terms": { "field": "SpecificationAttributes.SpecificationAttributeOptions.Name", "size": 100 } } } } } } } }, "SpecificationAttributesFirstFiltered": { "filter": { "bool": { "must": [ ] } }, "aggs": { "SpecificationAttributes": { "nested": { "path": "SpecificationAttributes" }, "aggs": { "Spec_group": { "terms": { "field": "SpecificationAttributes.Name", "size": 100 }, "aggs": { "Opctions": { "terms": { "field": "SpecificationAttributes.SpecificationAttributeOptions.Name", "size": 100 } } } } } } } } } } ``` I have query to find the product by name,SKU. I want to exclude the product which is IsParent is false when try to search by name but when its exact search by sku then all product need to be search. How to do it? Search by name is working fine with excluding isParent false products. My elastic query as below ```auto { "min_score": 810, "query": { "bool": { "must": [ { "term": { "Published": { "value": "true" } } }, { "terms": { "Stores.Id": [ 0, 1 ] } }, { "bool": { "must_not": { "term": { "IsParentProduct": "false" } } } }, { "bool": { "should": [ { "match_phrase": { "Name": { "query": "BGEBASIC33", "boost": 100 } } }, { "match": { "Name": { "query": "BGEBASIC33", "boost": 150 } } }, { "match_phrase": { "ProductCategories.Name.exact": { "query": "BGEBASIC33", "boost": 200 } } }, { "match": { "ProductCategories.Name.exact": { "query": "BGEBASIC33", "boost": 300 } } }, { "match_phrase": { "ParentCategory.Name": { "query": "BGEBASIC33", "boost": 1000 } } }, { "match": { "ParentCategory.Name": { "query": "BGEBASIC33", "boost": 750 } } }, { "match_phrase": { "ShortDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match": { "ShortDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match_phrase": { "FullDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match": { "FullDescription": { "query": "BGEBASIC33", "boost": 5 } } }, { "match_phrase": { "ProductManufacturer.Name.exact": { "query": "BGEBASIC33", "boost": 200 } } }, { "match": { "ProductManufacturer.Name.exact": { "query": "BGEBASIC33", "boost": 200 } } }, { "nested": { "path": "ProductPromoTags", "query": { "bool": { "must": [ { "bool": { "should": [ { "match_phrase": { "ProductPromoTags.Name": { "query": "BGEBASIC33", "boost": 100 } } }, { "match": { "ProductPromoTags.Name": { "query": "BGEBASIC33", "boost": 100 } } } ] } }, { "bool": { "should": [ { "match_phrase": { "ProductPromoTags.SystemName": { "query": "BGEBASIC33", "boost": 100 } } }, { "match": { "ProductPromoTags.SystemName": { "query": "BGEBASIC33", "boost": 100 } } } ] } } ] } } } }, { "match": { "Sku": { "query": "BGEBASIC33", "boost": 500 } } }, { "match_phrase": { "Sku": { "query": "BGEBASIC33", "boost": 100 } } }, { "nested": { "path": "SpecificationAttributes", "query": { "bool": { "should": [ { "match_phrase": { "SpecificationAttributes.SpecificationAttributeOptions.Name.exact": { "query": "BGEBASIC33", "boost": 80 } } }, { "match": { "SpecificationAttributes.SpecificationAttributeOptions.Name.exact": { "query": "BGEBASIC33", "boost": 100 } } } ] } } } } ] } } ] } }, "from": 0, "size": 32, "aggs": { "PriceRanges": { "range": { "field": "FinalPrice", "ranges": [ { "from": "0", "to": "100" }, { "from": "101", "to": "998" }, { "from": "999", "to": "1999" }, { "from": "2000", "to": "2999" }, { "from": "3000" } ] } }, "ProductCategoryCustomURL": { "terms": { "field": "ProductCategories.CustomURL", "size": 100 } }, "ProductManufacturer": { "terms": { "field": "ProductManufacturer.Name", "size": 100 } }, "SpecificationAttributesfiltered": { "filter": { "bool": { "must": [ ] } }, "aggs": { "SpecificationAttributes": { "nested": { "path": "SpecificationAttributes" }, "aggs": { "Spec_group": { "terms": { "field": "SpecificationAttributes.Name", "size": 100 }, "aggs": { "Opctions": { "terms": { "field": "SpecificationAttributes.SpecificationAttributeOptions.Name", "size": 100 } } } } } } } }, "SpecificationAttributesFirstFiltered": { "filter": { "bool": { "must": [ ] } }, "aggs": { "SpecificationAttributes": { "nested": { "path": "SpecificationAttributes" }, "aggs": { "Spec_group": { "terms": { "field": "SpecificationAttributes.Name", "size": 100 }, "aggs": { "Opctions": { "terms": { "field": "SpecificationAttributes.SpecificationAttributeOptions.Name", "size": 100 } } } } } } } } } } ``` ![Screenshot_1](https://us1.discourse-cdn.com/elastic/original/3X/4/c/4c9f531feab191d25cc5276149dfb70948b555a1.png) Main image is parent product. After image you can see the three round image and sequre box that are separate products that connect with main image product. Child products haveing property "IsChildProduct" that is set as true if its child. I want to exclude the child products in search as separate product that is working fine in my query. but if user want to search by child product sku,name etc then my query return emty result. Let me know what should I do to make this working. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 27, 2022, 1:23pm UTC](https://discuss.elastic.co/t/search-by-child-product-when-search-by-sku-in-elastic-search-query/313256/2 "2022-09-27T13:23:40Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.