Workaround for nested objects

[mirek]

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?

[Stacey_Gammon]

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?)

[Stacey_Gammon]

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

[Bargs (Matt Bargar)]

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?

[mirek]

@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?

[Stacey_Gammon]

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.

[mirek]

@Stacey_Gammon

your data structure and indexing these as separate

@Stacey_Gammon, yes that's exactly the same approach as I proposes in first post Unfortunatelly the I have to aggregate players by playerId using uniqueCount which is an approximation (not acceptable by business) - Finding Distinct Counts | Elasticsearch: The Definitive Guide [2.x] | Elastic