ElasticSearch multiple terms search json

Question

I have a ton of items in a Db with many columns. I need to search across two of these columns to get one data set. The first column, genericCode, would group together any of the rows that have that code. The second column, genericId, is calling out a specific row to add because it is missing the list of genericCode's i'm looking for.

The back-end C# sets up my json for me as follows, but it returns nothing.

{
  "from": 0,
  "size": 50,
  "aggs": {
    "paramA": {
      "nested": {
        "path": "paramA"
      },
      "aggs": {
        "names": {
          "terms": {
            "field": "paramA.name",
            "size": 10
          },
          "aggs": {
            "specValues": {
              "terms": {
                "field": "paramA.value",
                "size": 10
              }
            }
          }
        }
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "locationId": {
              "value": 1
            }
          }
        },
        {
          "terms": {
            "genericCode": [
              "10A",
              "20B"
            ]
          }
        },
        {
          "terms": {
            "genericId": [
              11223344
            ]
          }
        }
      ]
    }
  }
}

I get and empty result set. If I remove either of the "terms" I get what I would expect. So, I just need to combine those terms into one search.

I've gone through a lot of the documentation here: https://www.elastic.co/guide/en/elasticsearch/reference/current/search.html and still can't seem to find what I'm looking for.

Let's say I'm Jelly Belly and I want to create a bag of jelly beans with all the Star Wars and Disney jelly beans, and I also want to add all beans of the color green. That is basically what I'm trying to do.

EDIT: Changing the "must" to '"should"` isn't quite right either. I need it to be (in pseudo sql):

SELECT *
FROM myTable
Where locationId = 1
AND (
  genericCode = "this", "that
  OR
  genericId = 1234, 5678
  )

The locationId separates our data in an important way.

I found this post: elasticsearch bool query combine must with OR and it has gotten me closer, but not all the way there...

I've tried several iterations of should > must > should > must building this query and get varying results, but nothing accurate.

I believe You should use "should" instead of "must". must = AND. should = OR — rAndom69, May 14 '19 at 16:05
@rAndom69 Nailed it! *smh* I should have realized that sooner — jeffcodes, May 14 '19 at 16:21
bool queries can be nested (according to documentation), tho I don't think I ever used it. https://www.elastic.co/guide/en/elasticsearch/client/net-api/current/bool-queries.html. — rAndom69, May 14 '19 at 17:57

score 0 · Answer 1 · answered May 14 '19 at 18:35

Here is the query that is working. It helped when I realized I was passing in the wrong data for one of my parameters. doh

Nest the should inside the must as @khachik noted in the comment above. I had this some time ago but it wasn't working due to the above blunder.

{
  "from": 0,
  "size": 10,
  "aggs": {
    "specs": {
      "nested": {
        "path": "paramA"
      },
      "aggs": {
        "names": {
          "terms": {
            "field": "paramA.name",
            "size": 10
          },
          "aggs": {
            "specValues": {
              "terms": {
                "field": "paramA.value",
                "size": 10
              }
            }
          }
        }
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "locationId": {
              "value": 1
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "terms": {
                  "genericCode": [
                    "101",
                    "102"
                  ]
                }
              },
              {
                "terms": {
                  "genericId": [
                    3078711,
                    3119430
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

ElasticSearch multiple terms search json

1 Answers1