How do I write correct query in nested document?

I have two indices: country and person

PUT person 
{
  "mappings": {
    "properties": {
      "info": {
        "type": "nested",
        "properties": {
          "int_val": {"type": "integer"},
          "str_val": {"type": "text"},
          "field_id": {"type": "keyword"},
          "country_id": {"type": "keyword"}
        }
      }
    }
  }  
}


PUT country
{
  "mappings": {
    
    "properties": {
      "country_ids": {"type": "keyword"}
    }
  }
}

PUT country/_doc/user1
{
  "country_ids": ["111", "222", "333"]
}

PUT person/_doc/1
{
  "info": [
    {
      "field_id": "1000",
      "str_val": "Jack Kotlin",
      "country_id": "444"
    },
    {
      "field_id": "1000",
      "str_val": "Jack Martin",
      "country_id": "333"
    },
    {
      "field_id": "1001",
      "str_val": "Jack",
      "country_id": "111"
    },
    {
      "field_id": "2000",
      "int_val": 30,
      "country_id": "444"
    },
    {
      "field_id": "2000",
      "int_val": 30,
      "country_id": "333"
    },
    {
      "field_id": "2001",
      "int_val": 30,
      "country_id": "111"
    }
  ]
}

If user1 queries '(field_id=1000 with str_val="Jack") & (field_id=2000 with int_val="Jack")' following result must be return:

{
  "info": [
    {
      "field_id": "1000",
      "str_val": "Jack Martin",
      "country_id": "333"
    },
    {
      "field_id": "2000",
      "int_val": 30,
      "country_id": "333"
    }
  ]
}

Help me please!

I wrote a query for single part: (field_id=1000 with str_val="Jack")

GET person/_search
{
  "query": {
    "nested": {
      "path": "info",
      "query": {
        "bool": {
          "filter": [
            {
              "terms": {
                "info.country_id": {
                  "index": "country",
                  "id": "user1",
                  "path": "country_ids"
                }
              }
            }
          ],
          "must": [
            {"match": {"info.field_id": "1000"}},
            {"match": {"info.str_val": "Jack"}}
          ]
        }
      },
      "inner_hits": {}
    }
  },
  "_source": false
}

and got a correct result:

{
  "took" : 5,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.1530519,
    "hits" : [
      {
        "_index" : "person",
        "_id" : "1",
        "_score" : 1.1530519,
        "inner_hits" : {
          "info" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 1.1530519,
              "hits" : [
                {
                  "_index" : "person",
                  "_id" : "1",
                  "_nested" : {
                    "field" : "info",
                    "offset" : 1
                  },
                  "_score" : 1.1530519,
                  "_source" : {
                    "field_id" : "1000",
                    "str_val" : "Jack Martin",
                    "country_id" : "333"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

but I don't know how to write a query for multi part!

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