Workaround for nested objects

Hi

I'm working or reports in Kibana and recently I faced issues with lack of support for querying over nested types ( https://www.elastic.co/guide/en/kibana/current/nested-objects.html )

Let's assume I have following index with people

 {
        playerId: 123,
    	firstName: "John",
    	lastName: "Doe",
    	clubs:[
                { clubName: "Bayern", status: "active",position: "goal keeper"},
    	        { clubName: "Manchester United", status: "inactive", position: "defense" }]
    }

.
Can I create filter for "active" players" in "Bayern" in Kibana ?

I thought I have found a solution by flattening record (like below) but then I have to use UniqeCount over "playerId" fields. Unfortunatelly uniqueCount is only an approximation ( https://www.elastic.co/guide/en/elasticsearch/guide/current/cardinality.html )

{ playerId:123, firstName: "John", lastName: "Doe", clubName: "Bayern", status: "active", position: "goal keeper" }
{ playerId:123, firstName: "John", lastName: "Doe", clubName: "Manchester United", status: "inactive", position: "defense" }

.
Do you have any suggestion how to apporach this with Kibana?

You should be able to do this if you set your mapping type to clubs as nested. Then you can use a nested query.

Here is how I'm updating the mapping (before I index data, mappings can't be adjusted afterwards):

PUT soccerplayers
{
  "mappings": {
    "data": {
      "properties": {
        "clubs": {
          "type": "nested" 
        }
      }
    }
  }
}

Then I can use some special syntax to create a filter to give me the right data (I edit the Query DSL, I'm not sure how to do this in the query bar - @Bargs - would Kuery be able to handle nested queries like this?) :

{
  "query": {
    "nested": {
      "path": "clubs",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "clubs.clubName": "Bayern"
              }
            },
            {
              "match": {
                "clubs.status": "active"
              }
            }
          ]
        }
      }
    }
  }
}

Then I get back results where players are only active in Bayern, not Manchester United:

I am seeing an error, though it doesn't seem to affect the actual results:


(@bargs - any idea on that error?)

btw, here is some more information which goes over how to index and query nested objects: https://www.elastic.co/guide/en/elasticsearch/reference/5.6/nested.html

It's impossible to do a nested query with the lucene query syntax. I think we can add nested support to Kuery in a way that's transparent to the user, but we don't support it today. It's on my wishlist of things to implement in the future.

I'm not sure about that error, I haven't seen it before. Is it specific to this particular query/data set, or does it show up for any nested query?

@Stacey_Gammon Thank you for suggestions - indeeed this might help with querying for nested objects.

I was only wondering if it's possible to build such queries by uses who clicks on visualizaitons. For example if I have a Pie Chart with all clubs then I would like to click piece with "Bayern" and filter by Bayern. This approach would work in simple (non-nested) scenario.

Do you have an idea how to achieve it with nested query?

@Bargs Do you think that future release of Kuery might support such a scenario?

What about flattening your data structure and indexing these as separate documents, is that a possibility? e.g.

 {
        playerId: 123,
    	firstName: "John",
    	lastName: "Doe",
    	clubName: "Bayern",
       status: "active",
       position: "goal keeper"
    }
 {
        playerId: 123,
    	firstName: "John",
    	lastName: "Doe",
    	clubName: "Manchester United",
       status: "inactive",
       position: "defense"
    }

I think that would be easier to work with in Kibana.

@Stacey_Gammon, yes that's exactly the same approach as I proposes in first post :slight_smile: Unfortunatelly the I have to aggregate players by playerId using uniqueCount which is an approximation (not acceptable by business) - https://www.elastic.co/guide/en/elasticsearch/guide/current/cardinality.html

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