locked
Azure Search Select distinct column values from index table RRS feed

  • Question

  • Is there any distinct support with select in azure? (equivalent sql: select DISTINCT columnX from IndexTable)

    If yes then please let me know how to make a rest call with distinct select. My query is

    https://[searchUrl].search.windows.net/indexes/indexName/docs?$select=columnX&api-version=2016-09-01"

    It returns me duplicate values.

    Any help?

    Friday, March 24, 2017 6:48 AM

Answers

  • Hi Raza,

    While there is no direct analog to SELECT DISTINCT in Azure Search, it is possible to retrieve distinct field values using facets.

    https://[searchUrl].search.windows.net/indexes/indexName/docs?facet=columnX&api-version=2016-09-01

    However, you need to keep in mind that calculating facets can be very expensive for fields that have many distinct values.

    Can you share some more details about your scenario?

    Friday, March 24, 2017 9:13 PM
    Moderator

All replies

  • Hi Raza,

    While there is no direct analog to SELECT DISTINCT in Azure Search, it is possible to retrieve distinct field values using facets.

    https://[searchUrl].search.windows.net/indexes/indexName/docs?facet=columnX&api-version=2016-09-01

    However, you need to keep in mind that calculating facets can be very expensive for fields that have many distinct values.

    Can you share some more details about your scenario?

    Friday, March 24, 2017 9:13 PM
    Moderator
  • Hi Bruce,

    I have one question, how to get the distinct of the columns.

    {  
      "search": "search.in('abcd')",
    "searchFields": "name",    
      "orderby": "id desc"
    }

    by the above query i am getting the details with the name = 'abcd' in addition to this for example i am getting gender & Sal as well.

    how to get the distinct count of gender and sal values for the given name.

    Any help ?

    Regards,

    Sreehari

     


    Friday, July 27, 2018 10:05 AM
  • Hi Bruce,

    in the azure search

    {  
      "search": "search.in('abcd')",
    "searchFields": "name",    
      "orderby": "id desc"
    }

    used facets as well

                

     {  
     "search": "search.in('abcd')",
    "searchFields": "name", 

    "facets": [ "gender","sal"],  
      "orderby": "id desc"
    }

    here i am getting the total count of gender and sal, but need the distinct of gender, sal even though the same gender, sal is present.

    Please help.

    Regards.

    Sreehari

     

    Friday, July 27, 2018 11:24 AM
  • I'm not sure what you're trying to do. Can you please provide examples of the results you see, and the results you want?

    Also, please note that the search.in() function is meant for use in filters, not search queries. Your "search" parameter should just be "abcd".

    Monday, July 30, 2018 1:29 AM
    Moderator
  • Below are the input & output 

    Input :
    {  
    "search": "search.in('abcd')",
    "searchFields": "name", 
    "facets": [ "gender","sal"],  
    "orderby": "sal desc"
    }

    Output :
    "gender": [
                {
                    "count": 23,
                    "value": "Male"
                }
            ],
    "sal": [
                {
                    "count": 23,
                    "value": "2000"
                }
            ],
        "value": [
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
            {
                "name": "abcd",
                "gender": "Male",
                "sal": "2000",            
            },
        ]
    }


    By using the facets, here i am getting the total count of the records (23) only even though the gender and sal are the same values.

    My question is it should display total no of records (23) and also distinct of gender & sal should be 1 because in all the records the values are same.

    Please help on this.

    Monday, July 30, 2018 12:42 PM
  • This behavior is by design. The facet results display the total count of documents containing each unique value in the resultset. There is no support for distinct count; you would need to implement such logic in your application code.
    Tuesday, July 31, 2018 1:44 AM
    Moderator
  • Thanks a lot.
    Tuesday, July 31, 2018 1:27 PM