Query document and filter nested data

Elastic Stack Elasticsearch
1

Hello, im trying to extract data from this api/mapping: http://distribution.virk.dk/cvr-permanent/_mapping

I want to be able to search for a specific name, like this:

{
  "query": {
    "match": {
      "Vrvirksomhed.virksomhedMetadata.nyesteNavn.navn": "Company Name ApS"
    }
  }
}

But i also want to be able to filter these 2 fields:
Vrvirksomhed.deltagerRelation.organisationer.medlemsData.periode.gyldigFra
Vrvirksomhed.deltagerRelation.organisationer.medlemsData.periode.gyldigTil
They are inside nested, nested objects.
I have tried this:

{
  "query": {
    "nested": {
      "path": "deltagerRelation",
      "query": {
        "nested": {
          "path": "deltagerRelation.organisationer.medlemsData",
          "query": {
            "range": {
              "deltagerRelation.organisationer.medlemsData.gyldigTil": {
                "gte": "now"
              }
            }
          }
        }
      }
    }
  }
}

But it gives me this error:
{"error":"SearchPhaseExecutionException[Failed to execute phase [query], all shards failed; shardFailures {[pmqny7rkSgGg0SKj_Y1peQ][cvr-permanent-prod-20170205][0]: RemoteTransportException[[eu-west-1b-i-d3bed659][inet[/172.31.42.111:9300]][indices:data/read/search[phase/query]]]; nested: SearchParseException[[cvr-permanent-prod-20170205][0]: from[-1],size[-1]: Parse Failure [Failed to parse source [{\n\t\"query\": {\n \t\"nested\": {\n \t\t\"path\": \"deltagerRelation\",\n \t\t\"query\": {\n \t\t\t\"nested\": {\n \t\t\t\t\"path\": \"deltagerRelation.organisationer.medlemsData\",\n \t\t\t\t\"query\": {\n \t\t\t\t\t\"range\": {\n \t\t\t\t\t\t\"deltagerRelation.organisationer.medlemsData.gyldigTil\": {\n \t\t\t\t\t\t\t\"gte\": \"now\"\n \t\t\t\t\t\t}\n \t\t\t\t\t}\n \t\t\t\t}\n \t\t\t}\n \t\t}\n \t}\n\t}\n}]]]; nested: QueryParsingException[[cvr-permanent-prod-20170205] [nested] failed to find nested object under path [deltagerRelation]]; }{[aYp9rAm3RYqTt7LxWbT4Hw][cvr-permanent-prod-20170205][1]: SearchParseException[[cvr-permanent-prod-20170205][1]: from[-1],size[-1]: Parse Failure [Failed to parse source [{\n\t\"query\": {\n \t\"nested\": {\n \t\t\"path\": \"deltagerRelation\",\n \t\t\"query\": {\n \t\t\t\"nested\": {\n \t\t\t\t\"path\": \"deltagerRelation.organisationer.medlemsData\",\n \t\t\t\t\"query\": {\n \t\t\t\t\t\"range\": {\n \t\t\t\t\t\t\"deltagerRelation.organisationer.medlemsData.gyldigTil\": {\n \t\t\t\t\t\t\t\"gte\": \"now\"\n \t\t\t\t\t\t}\n \t\t\t\t\t}\n \t\t\t\t}\n \t\t\t}\n \t\t}\n \t}\n\t}\n}]]]; nested: QueryParsingException[[cvr-permanent-prod-20170205] [nested] failed to find nested object under path [deltagerRelation]]; }{[pmqny7rkSgGg0SKj_Y1peQ][cvr-permanent-prod-20170205][2]: RemoteTransportException[[eu-west-1b-i-d3bed659][inet[/172.31.42.111:9300]][indices:data/read/search[phase/query]]]; nested: SearchParseException[[cvr-permanent-prod-20170205][2]: from[-1],size[-1]: Parse Failure [Failed to parse source [{\n\t\"query\": {\n \t\"nested\": {\n \t\t\"path\": \"deltagerRelation\",\n \t\t\"query\": {\n \t\t\t\"nested\": {\n \t\t\t\t\"path\": \"deltagerRelation.organisationer.medlemsData\",\n \t\t\t\t\"query\": {\n \t\t\t\t\t\"range\": {\n \t\t\t\t\t\t\"deltagerRelation.organisationer.medlemsData.gyldigTil\": {\n \t\t\t\t\t\t\t\"gte\": \"now\"\n \t\t\t\t\t\t}\n \t\t\t\t\t}\n \t\t\t\t}\n \t\t\t}\n \t\t}\n \t}\n\t}\n}]]]; nested: QueryParsingException[[cvr-permanent-prod-20170205] [nested] failed to find nested object under path [deltagerRelation]]; }{[pmqny7rkSgGg0SKj_Y1peQ][cvr-permanent-prod-20170205][3]: RemoteTransportException[[eu-west-1b-i-d3bed659][inet[/172.31.42.111:9300]][indices:data/read/search[phase/query]]]; nested: SearchParseException[[cvr-permanent-prod-20170205][3]: from[-1],size[-1]: Parse Failure [Failed to parse source [{\n\t\"query\": {\n \t\"nested\": {\n \t\t\"path\": \"deltagerRelation\",\n \t\t\"query\": {\n \t\t\t\"nested\": {\n \t\t\t\t\"path\": \"deltagerRelation.organisationer.medlemsData\",\n \t\t\t\t\"query\": {\n \t\t\t\t\t\"range\": {\n \t\t\t\t\t\t\"deltagerRelation.organisationer.medlemsData.gyldigTil\": {\n \t\t\t\t\t\t\t\"gte\": \"now\"\n \t\t\t\t\t\t}\n \t\t\t\t\t}\n \t\t\t\t}\n \t\t\t}\n \t\t}\n \t}\n\t}\n}]]]; nested: QueryParsingException[[cvr-permanent-prod-20170205] [nested] failed to find nested object under path [deltagerRelation]]; }{[pmqny7rkSgGg0SKj_Y1peQ][cvr-permanent-prod-20170205][4]: RemoteTransportException[[eu-west-1b-i-d3bed659][inet[/172.31.42.111:9300]][indices:data/read/search[phase/query]]]; nested: SearchParseException[[cvr-permanent-prod-20170205][4]: from[-1],size[-1]: Parse Failure [Failed to parse source [{\n\t\"query\": {\n \t\"nested\": {\n \t\t\"path\": \"deltagerRelation\",\n \t\t\"query\": {\n \t\t\t\"nested\": {\n \t\t\t\t\"path\": \"deltagerRelation.organisationer.medlemsData\",\n \t\t\t\t\"query\": {\n \t\t\t\t\t\"range\": {\n \t\t\t\t\t\t\"deltagerRelation.organisationer.medlemsData.gyldigTil\": {\n \t\t\t\t\t\t\t\"gte\": \"now\"\n \t\t\t\t\t\t}\n \t\t\t\t\t}\n \t\t\t\t}\n \t\t\t}\n \t\t}\n \t}\n\t}\n}]]]; nested: QueryParsingException[[cvr-permanent-prod-20170205] [nested] failed to find nested object under path [deltagerRelation]]; }{[aYp9rAm3RYqTt7LxWbT4Hw][cvr-permanent-prod-20170205][5]: SearchParseException[[cvr-permanent-prod-20170205][5]: from[-1],size[-1]: Parse Failure [Failed to parse source [{\n\t\"query\": {\n \t\"nested\": {\n \t\t\"path\": \"deltagerRelation\",\n \t\t\"query\": {\n \t\t\t\"nested\": {\n \t\t\t\t\"path\": \"deltagerRelation.organisationer.medlemsData\",\n \t\t\t\t\"query\": {\n \t\t\t\t\t\"range\": {\n \t\t\t\t\t\t\"deltagerRelation.organisationer.medlemsData.gyldigTil\": {\n \t\t\t\t\t\t\t\"gte\": \"now\"\n \t\t\t\t\t\t}\n \t\t\t\t\t}\n \t\t\t\t}\n \t\t\t}\n \t\t}\n \t}\n\t}\n}]]]; nested: QueryParsingException[[cvr-permanent-prod-20170205] [nested] failed to find nested object under path [deltagerRelation]]; }]","status":400}

And i'm lost. :frowning:

2

Hi Jakob,

In your mapping I can see that the type "produktionsenhed" have the property "VrproduktionsEnhed.deltagerRelation" not marked as nested. If you are doing the search in the hole index maybe this property is messing with the query.

Can you try restricting your query to some type, for example, "virksomhed" or change "produktionsenhed.VrproduktionsEnhed.deltagerRelation" to be nested and see if it works?

Cheers,
LG

3

Hello Luiz, i have now added the "type", which makes more sense. Thanks. Still struggling with the rest. :slight_smile:

4

Might be because i dont understand ES yet.

I hit this endpoint:
http://distribution.virk.dk/cvr-permanent/virksomhed/_search

But now i have this query:

{
  "_source": ["Vrvirksomhed.virksomhedMetadata.nyesteNavn.navn","Vrvirksomhed.deltagerRelation.deltager.sidstOpdateret"],
  "query": { 
    "nested": {
      "path": "Vrvirksomhed.deltagerRelation",
      "query": {
        "nested": {
          "path": "Vrvirksomhed.deltagerRelation.deltager",
          "filter": {
            "bool" : {
              "must" : [
                {
                  "range": {
                    "Vrvirksomhed.deltagerRelation.deltager.sidstOpdateret": {
                      "gte": "now-1d"
                    }
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
}

And i get results like:

                    {
                        "deltager": {
                            "sidstOpdateret": "2006-12-29T15:31:21.000+01:00"
                        }
                    },
                    {
                        "deltager": {
                            "sidstOpdateret": "2017-11-30T14:09:00.000+01:00"
                        }
                    },

I thought that "filter" would remove the ones not matching the query ?

5

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