Count occurrences of each item in JSON

[fischera]

Hi - I want to return the count of each Status per Audience.
For example, I would like to return how much Mail has been sent to companies A, B or C, …
So for the following JSON, it would return 2 “Visit Profil” for Company_A, 1 “Visit Profil” for Company_B, 1"Mail sent" in Company_A, etc.

[
{
"Status ": "Visit Profil ",
"Audience": "Company_A"
},
{
"Status ": "Visit Profil",
"Audience": "Company_A"
},
{
"Status ": "Mail Sent",
"Audience": "Company_A"
},
{
"Status ": "Visit Profil ",
"Audience": "Company_B"
},

I’ve tried with an IF node but it looks like it’s not returning a count but an item and does not seem scalable as I’ve multiple IF criteria for both Status and Audiences.

I’m looking for a JavaSCript or simpler solution.

Thanks

[djangelic]

Hi, there is a pretty simple solution using the set node and the item list node. There is a way in the UI to pull the count of a number very easily but it’s a little bit hidden.

1. Before your set but after the IF put the Item List node. Use the Aggregate Items Operation.
2. 
3. In the Input Field Name put the name of the key that you want to count.
4. Next, go to the set Node and create a new number and call it something like count and hit add expression
5. 
6. Next, on the left hand side look for the aggregated values
7. 
8. In my case, I’m using Name. Notice how the Red “name” has a slightly bigger Grey bubble next to it.
9. 
10. Click on that bubble for some hidden options. The one we care about is length
11. 
12. It should then count all the array items.

Here is the mock workflow I used to build this for you to play with:

[fischera]

Thanks, @djangelic, great insight from you.
I’ve followed your instructions but I get this message :

Any idea?

[fischera]

@djangelic : I’ve solved it … there was a " " in the JSON Payload
Now, my concern is more about: How can I loop through every single parameter from my IF node ?
In this case they are 2 : Audience name and Status …
The idea is to have a clear status for every existing and new audience

[djangelic]

Awesome! Good job! I’m on mobile right now but I’ve been meaning to put together a video on looping that explains the process. I’ll try to post that in a bit. If not I’ll send you a mock-up of the process instead.

[fischera]

Excellent @djangelic ! 1000 thx
Do you mean by writing a function or is it possible through nodes?
I’ve found this useful resource with a similar usecase: javascript - Count occurrences of each item in JSON - Stack Overflow - But unable to turn it into something that works for me.

[djangelic]

@fischera

In this case they are 2 : Audience name and Status …

It’s possible using Nodes, I didn’t have time to put together a video but instead here is a mockup using my sheets to show you how it’s done. Delete the Loop Values node and replace it with whatever nodes you want to run within the loop, and delete the Loop Complete node, as that’s just telling you that the loop finishes here.

Please note that my mockup will not work in the desktop version. Just delete the function node at the beginning (Pretend I’m google sheets) and replace with your google sheet node instead.

Also open the nodes, and check the settings for more details as to what each node is doing and how.

[fischera]

Thanks for your reply. I’ve tried many times, but it seems not to work despite your explanations, but I’m sure I’m not far from the truth
From a global point of view, I understand your logic as follows: Read the GSheet > Set up the conditions with the IF node > Iterate over each value > Set up which value to loop into > Check if the iteration was done successfully.
Right?
Besides, why don’t you consider the first part of the Workflow as we need to count the items?
Here are my two majors problems :
1- “Iterates Values” - get this error message

Act as input value does not exist, but I don’t know why?
2- “Loop value here” - I’m unsure which data I need to consider here. To me, It would have to make sense to add the GSheet node as I wanted to iterate through every audience and every status

[djangelic]

Give me a bit to put a video together to explain this, I think it will do a better job.

[fischera]

Thx a lot !

[djangelic]

Hi @fischera, I think I misread your initial request. I think the new example I’m including below will do what you are trying to do. Here is also a video to explain it as well:

[fischera]

Thank you @djangelic this is exactly what I need.
Super useful and the logic is clear to me.
In the longer term, the only thing is that I will generate > 100 companies over time. Do you think I’ll need to create a No Operation node every time I have a new company in my Workflow, or do you see another way to use this Workflow at scale?

[djangelic]

Hi @fischera !

Glad that helped a little bit!

No, for longer term you will probably want to have someone with a bit more Javascript knowledge help you create a function that gives you the length of all similar strings based on their google sheet inputs without having to manually map each one out using the No-Operation node. Unfortunately my skills are not yet good enough to do that yet. Someone else in the community may know Javascript better to where they can help you write it. For now the best I can do to help is by showing you how to count manually defined arrays.

[fischera]

Thanks a lot for your support @djangelic, very nice of you.
At least I’ve learned useful things.
@MutedJam: Maybe something you can solve here?

[MutedJam]

Hey all, based on the example data from the original post my suggestion would look like so:

The example code I am using first extracts all unique values in the Audience and Status fields.

It then creates a response object for each audience and then adds a field for each status. The value of this field will be a count of how many occurrences there are matching both audience and status:

const unique_audiences = [...new Set(items.map(item => item.json.Audience))];
const unique_statuses = [...new Set(items.map(item => item.json.Status))];

let results = [];

unique_audiences.forEach(audience => {
  let result = {
    json: {
      audience: audience
    }
  };
  unique_statuses.forEach(status => {
    result.json[status] = items.filter(item => item.json.Audience == audience && item.json.Status == status).length;
  })
  results.push(result);
});

return results;

This leaves you with a result like this:

Is this what you had in mind @fischera?

[fischera]

EXACTLY!
Thanks a lot! I’m fascinated how a single line of code could save so much time.
What could you advise me to start learning JavaScript?

[djangelic]

You’re awesome @MutedJam !

Gonna save this snippet for future use!

[MutedJam]

Thanks, glad to hear this helped!

The way I approached this isn’t super specific to JavaScript. When seeing a logical problem I first try to break it down into smaller chunks. In this case, it was trying to understand what data pieces we need. And the first thing that came to my mind was a list of companies because we want to count something for each company. So I started like this:

• The first chunk so to speak was to extract a list of companies from your example array consisting of objects. So for this I’d then find out how to extract specific values from an array of objects (that’s what .map() does here, transforming each value in an array on the fly - in this case from a nested object to a simple string).
• The next step was then to remove duplicates, cause you typically want only one entry for each company. That’s something the Item Lists node could help with, but since I expected code to be required anyway I skipped using a separate node for this here and went with creating a unique_audiences array based on a Set (which is a storage for unique values) using the Spread syntax. At this stage I have my “unique company list”.

Now this might sound complex and like I have just learned these things at some point, but full disclosure, that’s not the case . So I typically search for the correct syntax or method when required (e.g. the first result for getting unique values from an array was this one, which lists some solutions). I’ve shared my go-to reference here, but any search engine of your choice will do the job as well.

So tl;dr I don’t have any great advice on learning JS unfortunately (it’s been quite a while since my college days and we focused on other languages back then). I just tried to transfer familiar concepts as good as possible (which worked out well so far).

I do however know that @BlueRabbit3 was thinking about working through a free JS course recently, maybe she already has some initial feedback and can share the course she was looking at?

[BlueRabbit3]

@fischera I totally get this! JavaScript is high up on my “to learn” list

I highly recommend Free Code Camp, they have a JavaScript course and I love their written format. I’m not much of a video person, so if you like to read and get hands on immediately, this is a great course.

[mcnaveen]

Also, This will help.

It has collection of useful JS Snippets with explanations.

JavaScript Snippets - 30 seconds of code