#community-help

Querying and Updating Facets in Typesense

TLDR Hafiz asked about sum/average of records, hourly grouping, and data formatting in Typesense. Kishore Nallan advised on using facet_by, grouping methods, and bulk update options. Hafiz was unable to get sum by group but was advised to store records pre-grouped.

Powered by Struct AI

1

1

31
9mo
Solved
Join the chat
Dec 07, 2022 (10 months ago)
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
10:28 AM
Hello everyone
Want to know that typesense support below queries or not:
• want to get sum or average of all records in query response
• can i get group data on hourly base with sum and total matched records
If above points are possible then how or any work around?
Thanks!
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
10:55 AM
The sum/average are available as facet response on integer fields. You have to facet_by on the field you wish to get the statistics on.
10:56
Kishore Nallan
10:56 AM
Grouping on hourly basis is not possible. I presume you want to round a timestamp field by hour and then group on that?
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
11:00 AM
what if i save data with formatting like
assign hour = 10 in all records between 10 am to 11 am
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
11:17 AM
Yeah that will work. You can then do group_by on that field and then do facet_by
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
11:19 AM
Thanks for your quick reply ❤️

1

Dec 12, 2022 (10 months ago)
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
07:36 AM
Kishore Nallan I tried your suggestion mentioned above and i got most of data as per requirement.
I have another scenario, for example i have amount column and i want to get the sum or average of that amount from all documents, can you please guide me on this?
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
07:42 AM
You can again do a facet_by on the amount column.
07:43
Kishore Nallan
07:43 AM
Send the query as * to target all documents.
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
07:43 AM
i tried using facet_by, but it was not giving the sum amount
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
07:44 AM
Facet response should contain min, max, count and sum.
07:44
Kishore Nallan
07:44 AM
For all numerical fields.
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
07:46 AM
"stats": {
                "avg": 7529.81586971587,
                "max": 8301874,
                "min": 0,
                "sum": 108655243,
                "total_values": 3162
            }

above mentioned value is sum?
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
07:49 AM
Yeah there is sum above
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
07:49 AM
thank you ❤️

1

Dec 27, 2022 (9 months ago)
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
08:01 AM
Kishore Nallan a new question 👀
i want to get sum by group. for example
I have a sales data which include product name total amount and date. I want to get the sum of total amount by dates
08:02
Hafiz
08:02 AM
{
"q": "*",
"query_by": "productName",
"facet_by": "totalAmount",
"group_by": "date"
}
applied above search, but not getting the desired results
08:06
Hafiz
08:06 AM
Also i am saving date as day of month
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
08:50 AM
Faceting provides a stats field which returns a sum value. However I can't recall off the top of my head whether this takes grouping into account? Can you try with a few sample records?
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
09:44 AM
i tried with sample record
09:44
Hafiz
09:44 AM
but not found something like this
09:51
Hafiz
09:51 AM
facet and group values provided separately in response
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
09:52 AM
Yeah so it's not possible to do what you want
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
09:53 AM
getting this response
{
    facet_counts: [],
    grouped_hits: [
        {
            group_key: [],
            hits: []
        }
    ]
}
09:53
Hafiz
09:53 AM
can suggest any work around for this?
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
09:54 AM
Facet cannot do stats on another field. You have to store records pre-grouped.
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
10:02 AM
maimum value in group_limit? i pass 100 and it response back
{
"name": "RequestMalformed",
"httpStatus": 400
}
10:04
Hafiz
10:04 AM
ok got it, just saw in console the maximum limit value i can pass is 99
01:04
Hafiz
01:04 PM
Kishore Nallan is there any possibility to bulk update documents?
Kishore Nallan
Photo of md5-4e872368b2b2668460205b409e95c2ea
Kishore Nallan
01:27 PM
Yes please check the documentation about imports via upsert/update action modes.
Hafiz
Photo of md5-cc74add1874277a1b2bbeac0afcacb12
Hafiz
01:58 PM
Thank you for your support