Elastic query for where in

Elastic Stack Elasticsearch
1

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

20220827_010842

2

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.

3

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?

4

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.

1 Like
5

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"
                    ]
                }
6

I found the solutions here

for my case this was it

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

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