I am pulling data from a REST API, and unfortunately the data I need is coming through the REST request as text as opposed to a number as seen below:
“total_price”: “500000000”,
I’ve been trying to figure out how to use the NUMBERS function in conjunction with “SUM_BY_KEY” however I have not had any luck. Does anyone have any tips?
Can anyone help me with this? I have tried using PLUCK function nested in a SUM function but the result always returns a null 0 value even thought I know there is data there. I have changed the schema in the REST API data connector for the field in question to “number” but the sum functions still do not seem to recognize the field as a number for the purposes of using SUM functions.
Hi, you can change a string to a number using NUMBER().
If your original data is saved in data.items, you can transform all total_price fields to numbers with MAP() and SET_KEY():
MAP(data.items, SET_KEY(item, "total_price", NUMBER(item.total_price))
Then use this transformed array in the SUM_BY_KEY formula:
SUM_BY_KEY(*previous formula here*, "total_price")
This is very helpful Mari thank you. One follow up.
I am further trying to filter the lists to sum, by matching value from the list to a binded app variable called “wallet.address”
The collection has a “sellers” object with the following structure:
“seller”: {
“user”: {
“username”: “usernamestring”
},
“profile_img_url”: “https://profileurl.png”,
“address”: “addressstring”,
“config”: “”
}
Using your instructions, I can sum the entire collection pulled from the API. I’m hoping to filter the collection before the SUM function so that I can summarize the total buying/selling activity of each user.
I am trying this: SUM_BY_KEY(SELECT((MAP(data.items, SET_KEY(item, “total_price”, NUMBER(item.total_price)))),“seller.address”==appVars.wallet_address),“total_price”) which returns a 0 value, even though the correct information in testing should be > 0 using known test data.
I think I’m doing something wrong with the select formula. Can you advise? I am an applied learning and your last post was immensely helpful in helping me understand how formulas and functions work.
Hi, you need to compare the current object property to the appVar, not a string – so item.address instead of "seller.address"
You can check out the documentation for SELECT here with some examples. 
Hi Mari - I have tried this as follows, with still no success.
I also reviewed the documentation and seem to be doing everything correct. Wondering if there are other practical examples anyone can show me so I can better understand how to get this working.
SUM_BY_KEY(MAP(SELECT(data.items, item.seller.address==appVars.wallet_address), SET_KEY(item, “total_price”, NUMBER(item.total_price))),“total_price”))
I’ve also tried testing with a static string that matches the variable data to no success:
SUM_BY_KEY(MAP(SELECT(data.items, item.seller.address==“address_string”), SET_KEY(item, “total_price”, NUMBER(item.total_price))),“total_price”))
Hi, looks like a syntax error to me, since there are 2 braces at the end of the formula – "total_price" should not be part of any other formula than SUM_BY_KEY:
SUM_BY_KEY(MAP(SELECT(...), SET_KEY(item, "total_price", NUMBER(...))), "total_price")
Still doesn’t work unfortunately. Not sure what I am doing wrong here.
Hi, are you getting any errors in red in the formula or do you get a valid preview of what the result should be? Can you post a screenshot of what the formula looks like atm, and your app variable & data variable schemas?
Hi Mari - here is the formula.
The data variable is being set via the following:
The URL parameters are set on a tap component from the previous page (these parameters work for passing the correct URL parameters into the REST API call that generates the list).
A sample of the schema coming from the API call is attached.sample response (27.0 KB)
Hi, I don’t completely understand the use case here, why are you setting the data variable as a number (SUM_BY_KEY)? How are the formulas related?
Sorry Mari, made a mistake in my post above.
The formula is shown in this screenshot:
The data variable is set by the following:
The use case, I would like to filter the data collection by the item “seller.address” so that we are summing only items that match that criteria. The formula works great without the SELECT function, however as soon as I include it I get a none value, even thought I have 100% confirmed that there is data being returned in the data that matches the selection criteria.
Hi, I think the problem might be with nesting MAP and SELECT and the item reference getting confused between the two, what if you try:
SUM_BY_KEY(MAP<m>(SELECT<s>(data.Opensea_events_sales1, s.winner_account_address === "..."), SET_KEY(m, "total_price", NUMBER(s.total_price)), "total_price")
Hi Mari - think the issue is with the SELECT not working properly with the data variable and the REST API Call. I’ve set up a specific test to illustrate
I have created a test data resource in my app that is a simple API call to the following url: https://api.opensea.io/api/v1/events?collection_slug=stoner-cats-official&account_address=0x72b62F6F66Fe33fA140F46678194c0CCa1e61cc4&event_type=successful&only_opensea=false&offset=0&limit=300
I have then added that data as a data variable to the test page I created below.
In the attached sample response, there is 4 items within the asset_events object. 3 of those items match the SELECT formula as displayed here: SELECT(data.Opensea_events_stonercats1,item.seller.address==“0x72b62F6F66Fe33fA140F46678194c0CCa1e61cc4”).
I have created a test page as follows:
So the data variable and API works a expected when unfiltered, but does not seem to work when using a simple SELECT to filter the list from the data variable:
As you can see, the list that uses the SELECT returns nothing, while the simple list works as expected.
This is likely the problem I am having with setting up the SUM_BY_KEY. Applying a SELECT seems to break the data variable for some reason and I can’t figure out why.
sample response (27.0 KB)
Hi, the formula seems to be correct, could it be that you are trying to use SELECT on the dataset before the data actually loads, and you end up with an empty list?
I don’t know how would i check this?
I also don’t think this is the problem because other formulas that use the same data work fine.
Hi, I tried setting up the same resource and it works – looks like in the comparison string you are using some of the letters are in uppercase, so it doesn’t match with the data.
Wow you are right Mari, it was case sensitivity. Added a LOWERCASE function to account for this. Thank you
