Filtering data returned from nested query

Elastic Stack Elasticsearch
1

Hello,

I am have a set of data in the following structure:

   [
        {
            "productId": "ProductId1",
            "customerNumbers": [
                "customer": {
                    "name": "Prod1Cust1",
                    "totalOrders": 23
                },
                "customer": {
                    "name": "Prod2Cust1",
                    "totalOrders": 5
                },
                "customer": {
                    "name": "Prod3Cust1",
                    "totalOrders": 5
                }
            ]
        },
        {
            "productId": "ProductId2",
            "customerNumbers": [
                "customer": {
                    "name": "Prod2Cust1",
                    "totalOrders": 23
                },
                "customer": {
                    "name": "Prod2Cust1",
                    "totalOrders": 5
                }
            ]
        }
    ]

and I need to fetch all the records which have a prefix of "Prod1 as in name field(in the example avoid, only first record should be returned i.e. ProductId1). Also, when the data is returned, I need to just fetch just the customer number whose prefix is Prod1 i.e:

Correct Output:

    {
            "productId": "ProductId1",
            "customerNumbers": [
                "customer": {
                    "name": "Prod1Cust1",
                    "totalOrders": 23
                }
            ]
        }

Instead of:

  {
            "productId": "ProductId1",
            "customerNumbers": [
                "customer": {
                    "name": "Prod1Cust1",
                    "totalOrders": 23
                },
                "customer": {
                    "name": "Prod2Cust1",
                    "totalOrders": 5
                },
                "customer": {
                    "name": "Prod3Cust1",
                    "totalOrders": 5
                }
            ]
        }

I'm able to fetch the records whose Name prefix is "Prod1" using nested query coupled with MatchPhrasePrefixQuery (this returns me result with all the customer numbers). How can I further filter the data to get customer numbers whose Name prefix is "Prod1".

Following is my current query:

  {
        "from": 0,
        "size": 10,
        "sort": [
            {
                "name.keyword": {
                    "missing": "_first",
                    "order": "asc"
                }
            }
        ],
        "query": {
            "bool": {
                "must": [
                    {
                        "bool": {
                            "must": [
                                {
                                    "nested": {
                                        "query": {
                                            "bool": {
                                                "must": [
                                                    {
                                                        "match": {
                                                            "customerNumbers.name": {
                                                                "query": "Prod1",
                                                                "type": "phrase_prefix"
                                                            }
                                                        }
                                                    }
                                                ]
                                            }
                                        },
                                        "path": "customerNumbers"
                                    }
                                }
                            ]
                        }
                    }
                ]
            }
        }
    }

P.S: I'm using ElasticSearch 5.x with Nest.

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.