Azure AI Search Lucene Query Syntax Error

I am trying to learn Azure AI search on my database table. I have following dataset.

I can do a search (Lucene) as follows and it works fine:

{
  "queryType": "full",
  "search": "Subject_Name:Math",
  "count": true
}

But when I make it as follows, it shows an error "InvalidSearchSyntax: Invalid search against numeric field. Only numeric values are allowed for search in numeric fields."

{
  "queryType": "full",
  "search": "Subject_Name:Math AND Marks:>10",
  "count": true
}

1. How do I find students who has latest Math score greater than 10?
2. How do I find students who has improved 5 or more score in last Math test compared to the previous Math test?

References:

1. Examples of "full" Lucene search syntax
2. Lucene query syntax in Azure AI Search
3. Examples of "simple" search queries in Azure AI Search
4. Filters in keyword search 5.How to model relational SQL data for import and indexing in Azure AI Search

When you do query search it only searches in string fields and you also specified the field name Subject_Name to search. But Math is of double or int in your case.

So, you need to do filter on that field.

Enable Filterable on the fields to filter it.

Next, query like below.

{
  "queryType": "full",
  "search": "Subject_Name:Math",
  "filter": "Marks gt 10",
  "count": true
}

and output for you sample data.

{
  "@odata.context": "https://xyz.search.windows.net/indexes('marks')/$metadata#docs(*)",
  "@odata.count": 3,
  "value": [
    {
      "@search.score": 0.6931472,
      "Unique_Record_ID": 1507373411,
      "Student_ID": 681046,
      "Subject_Name": "Math",
      "Marks": 42,
      "Test_Date": "2023-08-23T00:00:00Z",
      "Result_Reported_Date": "2023-08-23T00:00:00Z",
      "ETLInsertTS": "2023-08-24T04:19:00Z",
      "AzureSearch_DocumentKey": "aHR0cHM6Ly92amd2OzQ1"
    },
    {
      "@search.score": 0.2876821,
      "Unique_Record_ID": 152442821,
      "Student_ID": 681046,
      "Subject_Name": "Math",
      "Marks": 48,
      "Test_Date": "2023-12-06T00:00:00Z",
      "Result_Reported_Date": "2023-12-06T00:00:00Z",
      "ETLInsertTS": "2023-12-07T04:19:00Z",
      "AzureSearch_DocumentKey": "aHR0cHM6Ly92amnMuY3N2OzM1"
    },
    {
      "@search.score": 0.2876821,
      "Unique_Record_ID": 1425813725,
      "Student_ID": 681046,
      "Subject_Name": "Math",
      "Marks": 65,
      "Test_Date": "2022-06-09T00:00:00Z",
      "Result_Reported_Date": "2022-06-09T00:00:00Z",
      "ETLInsertTS": "2022-06-10T04:17:00Z",
      "AzureSearch_DocumentKey": "aHR0cHM6Ly92aRlcnMuY3N2OzU1"
    }
  ]
}

Next create new column called improved score by doing aggregation to get improved score, since ai search doesn't support such kind of operations.

Then you filter on that field like below.

{
  "queryType": "full",
  "search": "Subject_Name:Math",
  "filter": "Score_Improvement ge 5",
  "count": true
}