Order results by value in one of the array entries

Elastic Stack Elasticsearch
1

Hi,

I'm trying to order the result of a query by a specified entry in a array.

Here is a sample entry

{
"product_name": "product alfa",
"product_id": "4a86c92ccd26111d7ba0eada7da6a75af",
"description": "This is a sample product",
"image_id": "product_a.jpg",
"inventory": [
{
"warehouse": "warehouse_a",
"stock": 99
},
{
"warehouse": "warehouse_b",
"stock": 19
},
{
"warehouse": "warehouse_c",
"stock": 99
}
]
}

If there were more "products" containing alfa, I would (for example) want
to sort they by the stock of a warehouse.

I'm currently using a query like:

POST _search
{
"query": {
"match": {
"product_name":{
"query":"alfa",
"type" : "phrase"
}
}
},
"filter": {
"bool": {
"must": [
{
"term": {
"availability.warehouse": "warehouse_a"
}
}
]
}
}
}

I would like the results sorted by stock (for warehouse_a only) descending.

Any ideas?

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/01a7baad-40e3-40b3-8104-66910762b004%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

2

Hi Johan,

You try to use script based sorting.

Or the function score query.

I hope this helps.

Regards,

Jun Ohtani
johtani@gmail.com
blog : http://blog.johtani.info
twitter : http://twitter.com/johtani

2014/01/07 19:45、Johan E johane73@gmail.com のメール：

Hi,

I'm trying to order the result of a query by a specified entry in a array.

Here is a sample entry

{
"product_name": "product alfa",
"product_id": "4a86c92ccd26111d7ba0eada7da6a75af",
"description": "This is a sample product",
"image_id": "product_a.jpg",
"inventory": [
{
"warehouse": "warehouse_a",
"stock": 99
},
{
"warehouse": "warehouse_b",
"stock": 19
},
{
"warehouse": "warehouse_c",
"stock": 99
}
]
}

If there were more "products" containing alfa, I would (for example) want to sort they by the stock of a warehouse.

I'm currently using a query like:

POST _search
{
"query": {
"match": {
"product_name":{
"query":"alfa",
"type" : "phrase"
}
}
},
"filter": {
"bool": {
"must": [
{
"term": {
"availability.warehouse": "warehouse_a"
}
}
]
}
}
}

I would like the results sorted by stock (for warehouse_a only) descending.

Any ideas?

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/01a7baad-40e3-40b3-8104-66910762b004%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

3

Hi Jun,

Thanks for your reply.

Im not sure how I can get that to work. In my project I need to only
boost/order by the stock of "warehouse_a", how do I use only the value for
that entry in the array?

Thanks
Johan

On Wednesday, January 8, 2014 4:35:50 AM UTC, Jun Ohtani wrote:

Hi Johan,

You try to use script based sorting.

Elasticsearch Platform — Find real-time answers at scale | Elastic

Or the function score query.

Elasticsearch Platform — Find real-time answers at scale | Elastic

I hope this helps.

Regards,

Jun Ohtani
joh...@gmail.com <javascript:>
blog : http://blog.johtani.info
twitter : http://twitter.com/johtani

2014/01/07 19:45、Johan E <joha...@gmail.com <javascript:>> のメール：

Hi,

I'm trying to order the result of a query by a specified entry in a
array.

Here is a sample entry

{
"product_name": "product alfa",
"product_id": "4a86c92ccd26111d7ba0eada7da6a75af",
"description": "This is a sample product",
"image_id": "product_a.jpg",
"inventory": [
{
"warehouse": "warehouse_a",
"stock": 99
},
{
"warehouse": "warehouse_b",
"stock": 19
},
{
"warehouse": "warehouse_c",
"stock": 99
}
]
}

If there were more "products" containing alfa, I would (for example)
want to sort they by the stock of a warehouse.

I'm currently using a query like:

POST _search
{
"query": {
"match": {
"product_name":{
"query":"alfa",
"type" : "phrase"
}
}
},
"filter": {
"bool": {
"must": [
{
"term": {
"availability.warehouse": "warehouse_a"
}
}
]
}
}
}

I would like the results sorted by stock (for warehouse_a only)
descending.

Any ideas?

--
You received this message because you are subscribed to the Google
Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/01a7baad-40e3-40b3-8104-66910762b004%40googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/0eb410ef-0117-4004-84f0-713b5b02616f%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

4

I ended up changing the format of the json, with warehouse stock in
separate entries in an array. This way I can check for it and get the stock
at the same time.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/6c2103ee-7786-4ec0-b981-10aabb365fb9%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

5

Hi Johan,

Sorry for the late reply.

script base sort sample like this.

--sample--
POST /script_order_sample/_search
{
"query": {
"match": {
"product_name":{
"query":"alfa",
"type" : "phrase"
}
}
},
"filter": {
"bool": {
"must": [
{
"term": {
"inventory.warehouse": "warehouse_a"
}
}
]
}
},
"sort": {
"_script" : {
"script" : "foreach( stock_info : _source.inventory){ if (stock_info.warehouse == 'warehouse_a') {return stock_info.stock;}}",
"type" : "number",
"order" : "desc"
}
}
}

—

I have another plan for this issue.
I think that “inventory” array is flatten like this.
Ant your query only specify “sort” to “warehourse_a”.

{
"product_name": "product alfa",
"product_id": "4a86c92ccd26111d7ba0eada7da6a75af",
"description": "This is a sample product",
"image_id": "product_a.jpg",
"warehouse_a”: 99,
“warehouse_b": 19,
“warehouse_c”: 99
}

I hope it helps your issue.
Regards,

Jun Ohtani
johtani@gmail.com
blog : http://blog.johtani.info
twitter : http://twitter.com/johtani

2014/01/08 20:11、Johan E johane73@gmail.com のメール：

Hi Jun,

Thanks for your reply.

Im not sure how I can get that to work. In my project I need to only boost/order by the stock of "warehouse_a", how do I use only the value for that entry in the array?

Thanks
Johan

On Wednesday, January 8, 2014 4:35:50 AM UTC, Jun Ohtani wrote:
Hi Johan,

You try to use script based sorting.
Elasticsearch Platform — Find real-time answers at scale | Elastic

Or the function score query.
Elasticsearch Platform — Find real-time answers at scale | Elastic

I hope this helps.

Regards,

Jun Ohtani
joh...@gmail.com
blog : http://blog.johtani.info
twitter : http://twitter.com/johtani

2014/01/07 19:45、Johan E joha...@gmail.com のメール：

Hi,

I'm trying to order the result of a query by a specified entry in a array.

Here is a sample entry

{
"product_name": "product alfa",
"product_id": "4a86c92ccd26111d7ba0eada7da6a75af",
"description": "This is a sample product",
"image_id": "product_a.jpg",
"inventory": [
{
"warehouse": "warehouse_a",
"stock": 99
},
{
"warehouse": "warehouse_b",
"stock": 19
},
{
"warehouse": "warehouse_c",
"stock": 99
}
]
}

If there were more "products" containing alfa, I would (for example) want to sort they by the stock of a warehouse.

I'm currently using a query like:

POST _search
{
"query": {
"match": {
"product_name":{
"query":"alfa",
"type" : "phrase"
}
}
},
"filter": {
"bool": {
"must": [
{
"term": {
"availability.warehouse": "warehouse_a"
}
}
]
}
}
}

I would like the results sorted by stock (for warehouse_a only) descending.

Any ideas?

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elasticsearc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/01a7baad-40e3-40b3-8104-66910762b004%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email toelasticsearch+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/0eb410ef-0117-4004-84f0-713b5b02616f%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.