Elastic query for where in

[Harrowmykel (Harrow West)]

Hello,

Can someone give me the json query equivalent of the following MySQL query

Select * from Table where col1=q or col2=q where cat in (cat1, cat2, cat3)

Basically I want to find "q" in index "Table" but restricted documents that belong to a certain category "cat1", "cat2", cat3. I want to search in fields col1 and col2

[RabBit_BR (andre.coelho)]

Hi @Harrowmykel .

My suggestion:

{
  "query": {
    "bool": {
      "filter": {
        "terms": {
          "cat": [
            "a",
            "a"
          ]
        }
      },
      "should": [
        {
          "match": {
            "col_1": "q"
          }
        },
        {
          "match": {
            "col_2": "q"
          }
        }
      ]
    }
  }
}

Read more about bool queries.

[Harrowmykel (Harrow West)]

Thanks a lot for the response @RabBit_BR . How can I make it case-insensitive and also fuzzy? So that it is also spelling-error tolerant?

[xeraa]

text fields are by default case-insensitive and match has a parameter fuzziness. But for a more detailed answer you'll need to provide some sample documents with specifics what should and shouldn't match.

PS: No screenshots of code and format your code, please.

[Harrowmykel (Harrow West)]

Hello @xeraa

When I search I want to only search documents with category_shortcode 'yoruba' and I would like a case insensitive and spelling error tolerant selection

This is what a document looks like

                "_source": {
                    "id": "54",
                    "video_title": "Wuraola",
                    "shortcode": "54-wuraola",
                    "description": "Doyin blesses every person she comes along with but she's a sickler. She leaves her home when she realises she is unwanted and comes in contact with some people whom she blessed. Now that her Father is feeling her absence, he wants her back so also the other people too her desperate to have Doyin. \n\n FOLLOW on IG. TW and FB: @YORUBAHOOD\n\nTo enjoy more of our movies, click here: https:\/\/www.youtube.com\/user\/modexy234",
                    "youtube_id": "sC31YGEjYCw",
                    "duration": "5985",
                    "formatted_duration": "01:39:45",
                    "channel_id": "3",
                    "favorite_count": "174",
                    "comments_count": "62",
                    "views_count": "174",
                    "dislike_count": "0",
                    "rating": "174",
                    "is_trailer": "0",
                    "last_refresh_time": "1660939202",
                    "original_title": "Wuraola - Latest Yoruba Movie Drama 2016",
                    "active": "1",
                    "deleted_on_youtube": "0",
                    "original_description": "Doyin blesses every person she comes along with but she's a sickler. She leaves her home when she realises she is unwanted and comes in contact with some people whom she blessed. Now that her Father is feeling her absence, he wants her back so also the other people too her desperate to have Doyin. \n\n FOLLOW on IG. TW and FB: @YORUBAHOOD\n\nTo enjoy more of our movies, click here: https:\/\/www.youtube.com\/user\/modexy234",
                    "tags": "TheOkintv,irokotv|NOLLYWOOD,Yoruba 2016 Latest Movies,Yoruba5star Nollywood Movies,Yorubamagic 2016,Realnollymovie,Newly Uploaded 2016 Yoruba Movies,inside ibakatv 2016,iBAKATV|YORUBA,iBAKATV|NOLLYWOOD,TheOkintv Latest Yoruba 2016 Movie,Nollywood PicturesTV,Nigerian Yoruba Nollywood Movies,OlekuTV,ApataTV+,Geledetvhits,Latest Yoruba 2016 Movies,Oleku Tv Latest Yoruba Movie LibraTV,Latest Yoruba Movie 2016 Releases This Week,GeledeTV+,LibraTV",
                    "create_time": "1660939202",
                    "youtube_create_time": "1476434296",
                    "category_shortcodes": [
                        "drama",
                        "yoruba",
                        "romantic",
                        "intriguing",
                        "2016"
                    ],
                    "category_ids": [
                        "2",
                        "3",
                        "4",
                        "8",
                        "13"
                    ],
                    "actor_shortcodes": [
                        "drama",
                        "yoruba",
                        "romantic",
                        "intriguing",
                        "2016"
                    ],
                    "actor_ids": [
                        "2",
                        "3",
                        "4",
                        "8",
                        "13"
                    ]
                }

[Harrowmykel (Harrow West)]

I found the solutions here

r/elasticsearch - How to filter a multi match query?

for my case this was it

{
    "query": {
        "bool": {
            "must": [
                {
                    "multi_match": {
                        "fields": [
                            "col1",
                            "col2",
                            "col3"
                        ],
                        "query": "q",
                        "fuzziness": "AUTO"
                    }
                }
            ],
            "filter": [
                {
                    "terms": {
                        "cat": [
                            "a", 
                            "b"
                        ]
                    }
                },
            ]
        }
    }
}