Question 108830 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to write a JSONata query to get sum of values related to another distinct attribute?

I have been having trouble finding an appropriate solution for this as I have little experience working with JSON arrays and querying json with JSONata

Here is a simplified version of my JSON array:

[
  {
    "job":1,
    "issue":"red",
    "issuesInJob":50
  },
  {
    "job":1,
    "issue":"blue",
    "issuesInJob":50
  },
  {
    "job":2,
    "issue":"orange",
    "issuesInJob":20
  },
  {
    "job":2,
    "issue":"orange",
    "issuesInJob":20
  },
  {
    "job":3,
    "issue":"green",
    "issuesInJob":20
  },
    ..........
]

Explaining the array

There are different "job"s which have an assigned number. Each object in the array is meant to show a type of "issue" that happened in the job, example: "green" or "red" or "orange". Each job can have multiple issues, even multiple issues of the same type.

"issuesInJob" is the total number of issues in the job, meaning that if the value is 50 for job 1, then there are 50 objects in the array with the pair "job":1

Desired output

I would like to get the sum of the total number of issuesInJob for all the jobs, knowing that for each job, issuesInJob must be counted only once (one distinct value for issuesInJob for each job), and that two distinct job may have the same number of issues.

For example, the sum in the part of the array that I provided would be: 50 + 20 + 20 = 90

I initially summed up every distinct issuesInJob, but since two different jobs can have the same number of issues, it wouldn’t work.

One thing to note is I can’t change the structure of the array, which would’ve been simpler to get the desired results easier, I have to work around it.

Tags:

2

Answers


  1. 0

    You can achieve the desired result by using a JSONata expression that groups the data by the ‘job’ attribute and then sums up the distinct ‘issuesInJob’ values for each job. Here’s the JSONata expression to accomplish this:

    $sum($map($distinct(data.job), function($v, $i, $a) { $lookup($filter(data, function($v2, $i2, $a2) { $v2.job = $v }), 'issuesInJob')[0] }))

    You can break down the expression as follows:

    • $distinct(data.job): Get distinct job values.
    • $map(…): Map over the distinct job values.
    • $filter(…): Filter the original data array to get items with the current job value.
    • $lookup(…): Get the ‘issuesInJob’ value for the first item in the filtered array.
    • $sum(…): Sum up the ‘issuesInJob’ values for all distinct jobs.

    You can check out this expression in Stedi’s JSONata Playground: https://stedi.link/ben201H

    Login or Signup to reply.
  2. 0

    You can use the $reduce function to get only the first entry for each job and remember the already-seen jobs in the accumulator:

    $reduce(data, function ($acc, $item) {
  $item.job in $acc.seenJobs
    ? $acc
    : {
        "seenJobs": $append($acc.seenJobs, [$item.job]),
        "total": $acc.total + $item.issuesInJob 
      }
}, { "seenJobs": [], "total": 0 }).total

    See it on the Playground: https://stedi.link/ZzWncEc

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search