LEE
Reputation: 3605
Retrieve object with a matching attribute in an array - Elasticsearch
The JSON that I execute the query on is as below.
What should be the query if I only want to retrieve the variant (which is an array of objects) with an object which has a price of 82.00.
[
{
"id":"Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0LzIyODA5MjA1MTQ4NQ==",
"createdAt":"2017-10-23T11:05:40Z",
"updatedAt":"2018-03-10T05:24:40Z",
"descriptionHtml":"Et blanditiis autem. Molestiae delectus vero voluptatem libero cum. Aliquam tempore ex id sed aut excepturi facilis sunt.",
"description":"Et blanditiis autem. Molestiae delectus vero voluptatem libero cum. Aliquam tempore ex id sed aut excepturi facilis sunt.",
"handle":"future-proofed-re-engineered-handmade-cotton-chicken",
"productType":"Computers",
"title":"Future-proofed Re-engineered Handmade Cotton Chicken",
"vendor":"Bednar LLC",
"tags":[
{
"value":"data-generator"
}
],
"publishedAt":"2018-03-07T08:25:38Z",
"onlineStoreUrl":null,
"options":[
{
"id":"Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0T3B0aW9uLzM2NTQ0NDI2ODA2MQ==",
"name":"Title",
"values":[
{
"value":"Concrete blue - 71y-9a6"
},
{
"value":"Concrete teal - oxh-g1z"
}
]
}
],
"images":[
],
"variants":[
{
"id":"Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0VmFyaWFudC8zMzczOTAzODcyMDI5",
"title":"Concrete blue - 71y-9a6",
"price":"82.00",
"weight":14,
"available":true,
"sku":"",
"compareAtPrice":null,
"image":null,
"selectedOptions":[
{
"name":"Title",
"value":"Concrete blue - 71y-9a6"
}
],
"product":{
"id":"Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0LzIyODA5MjA1MTQ4NQ==",
"images":[
]
}
},
{
"id":"Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0VmFyaWFudC8zMzczOTA0MDAzMTAx",
"title":"Concrete teal - oxh-g1z",
"price":"196.00",
"weight":7,
"available":true,
"sku":"",
"compareAtPrice":null,
"image":null,
"selectedOptions":[
{
"name":"Title",
"value":"Concrete teal - oxh-g1z"
}
],
"product":{
"id":"Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0LzIyODA5MjA1MTQ4NQ==",
"images":[
]
}
}
]
}
]
I'm currently using the below way to index data.
var bulkIndex = function bulkIndex(index, type, data) {
let bulkBody = [];
data.forEach(item => {
bulkBody.push({
index: {
_index: index,
_type: type,
_id: item.id
}
});
bulkBody.push(item);
});
esClient.bulk({body: bulkBody})
.then(response => {
let errorCount = 0;
response.items.forEach(item => {
if (item.index && item.index.error) {
console.log(++errorCount, item.index.error);
}
});
console.log(`Successfully indexed ${data.length - errorCount} out of ${data.length} items`);
})
.catch(console.err);
};
Mapping created is as follows.
{
"shopify": {
"mappings": {
"products": {
"properties": {
"createdAt": {
"type": "date"
},
"description": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"descriptionHtml": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"handle": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"images": {
"properties": {
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"src": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"options": {
"properties": {
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"values": {
"properties": {
"value": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
},
"productType": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"publishedAt": {
"type": "date"
},
"tags": {
"properties": {
"value": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"title": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"updatedAt": {
"type": "date"
},
"variants": {
"properties": {
"available": {
"type": "boolean"
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"price": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product": {
"properties": {
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"images": {
"properties": {
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"src": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
},
"selectedOptions": {
"properties": {
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"value": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"sku": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"title": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"weight": {
"type": "long"
}
}
},
"vendor": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
}
}
Upvotes: 1
Views: 55
Answers (1)
Ra Ka
Reputation: 3055
As I am understanding, you want to retrieve document with variants items which has price of "82.00" i.e. variant - "Concrete blue - 71y-9a6" and not "Concrete teal - oxh-g1z" in document above.
In a regular scenario, if you search for document with variant price "82.00", it will find and return all document that match variant price and as well as other variants item in array which price is not "82.00".
{
"query" : {
"match":{
"variants.price" :"82.00"
}
}
}
//Will return above document with variants items both "Concrete blue - 71y-9a6" and "Concrete teal - oxh-g1z".
However, if you want to retrieve only variant item with price "82.00", you need to able to query that variant item separately and the solution is to use nested datatype in your doucument mapping.
The nested type is a specialized version of the object datatype that allows arrays of objects to be indexed and queried independently of each other.
{
"mappings": {
"foo": {
"properties": {
"createdAt": {
"type": "date"
},
"description": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"descriptionHtml": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"handle": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"options": {
"properties": {
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"values": {
"properties": {
"value": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
},
"productType": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"publishedAt": {
"type": "date"
},
"tags": {
"properties": {
"value": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"title": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"updatedAt": {
"type": "date"
},
"variants": {
"type": "nested",
"properties": {
"available": {
"type": "boolean"
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"price": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"product": {
"properties": {
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"selectedOptions": {
"properties": {
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"value": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"sku": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"title": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"weight": {
"type": "long"
}
}
},
"vendor": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
}
Querying your document:
{
"query": {
"nested": {
"path": "variants",
"query": {
"match": {
"variants.price": "82.00"
}
},
"inner_hits": {}
}
}
}
In above query, the variants nested field is explicitly queried with variant price "82.00". Note that, inner_hits allow us to highlight the matching nested documents because, elastic return the complete document along with all variant items, the inner_hits will contains only matched variant item (this is what we need). Also, you can limit field projection with _source (e.g. you may not want variants field root document but only variant items in inner_hits).
{
"took": 45,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 1,
"max_score": 0.6931472,
"hits": [
{
"_index": "foo",
"_type": "foo",
"_id": "1",
"_score": 0.6931472,
"_source": {
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0LzIyODA5MjA1MTQ4NQ==",
"createdAt": "2017-10-23T11:05:40Z",
"updatedAt": "2018-03-10T05:24:40Z",
"descriptionHtml": "Et blanditiis autem. Molestiae delectus vero voluptatem libero cum. Aliquam tempore ex id sed aut excepturi facilis sunt.",
"description": "Et blanditiis autem. Molestiae delectus vero voluptatem libero cum. Aliquam tempore ex id sed aut excepturi facilis sunt.",
"handle": "future-proofed-re-engineered-handmade-cotton-chicken",
"productType": "Computers",
"title": "Future-proofed Re-engineered Handmade Cotton Chicken",
"vendor": "Bednar LLC",
"tags": [
{
"value": "data-generator"
}
],
"publishedAt": "2018-03-07T08:25:38Z",
"onlineStoreUrl": null,
"options": [
{
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0T3B0aW9uLzM2NTQ0NDI2ODA2MQ==",
"name": "Title",
"values": [
{
"value": "Concrete blue - 71y-9a6"
},
{
"value": "Concrete teal - oxh-g1z"
}
]
}
],
"images": [],
"variants": [
{
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0VmFyaWFudC8zMzczOTAzODcyMDI5",
"title": "Concrete blue - 71y-9a6",
"price": "82.00",
"weight": 14,
"available": true,
"sku": "",
"compareAtPrice": null,
"image": null,
"selectedOptions": [
{
"name": "Title",
"value": "Concrete blue - 71y-9a6"
}
],
"product": {
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0LzIyODA5MjA1MTQ4NQ==",
"images": []
}
},
{
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0VmFyaWFudC8zMzczOTA0MDAzMTAx",
"title": "Concrete teal - oxh-g1z",
"price": "196.00",
"weight": 7,
"available": true,
"sku": "",
"compareAtPrice": null,
"image": null,
"selectedOptions": [
{
"name": "Title",
"value": "Concrete teal - oxh-g1z"
}
],
"product": {
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0LzIyODA5MjA1MTQ4NQ==",
"images": []
}
}
]
},
"inner_hits": {
"variants": {
"hits": {
"total": 1,
"max_score": 0.6931472,
"hits": [
{
"_nested": {
"field": "variants",
"offset": 0
},
"_score": 0.6931472,
"_source": {
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0VmFyaWFudC8zMzczOTAzODcyMDI5",
"title": "Concrete blue - 71y-9a6",
"price": "82.00",
"weight": 14,
"available": true,
"sku": "",
"compareAtPrice": null,
"image": null,
"selectedOptions": [
{
"name": "Title",
"value": "Concrete blue - 71y-9a6"
}
],
"product": {
"id": "Z2lkOi8vc2hvcGlmeS9Qcm9kdWN0LzIyODA5MjA1MTQ4NQ==",
"images": []
}
}
}
]
}
}
}
}
]
}
}
Upvotes: 2
Related Questions
- Elasticsearch query by value in array
- Elasticsearch match element in array
- search in array of objects in elasticsearch
- Match an array element in the array of json objects in elasticsearch
- Searching array of objects in Elasticsearch
- Is it possible to retrieve an object in an array that matches my query using elasticsearch?
- Elasticsearch: Query an array element
- Matching by array elements in Elasticsearch
- select matching objects from array in elasticsearch
- Retrieve matched array element in Elastic Search query