0

I am trying to group by a key and then calculate the sums for those groups (as shown in this example: JSONata (or JS) - group and sum JSON array / objects). Then I would like to divide the original number by the group sum.

Sample:

{
    "positions": [
        {
            "ticker": "AAPL",
            "marketValue": 100
        },
        {
            "ticker": "AAPL",
            "marketValue": 200
        },
        {
            "ticker": "ATVI",
            "marketValue": 200
        },
        {
            "ticker": "ATVI",
            "marketValue": 300
        },
        {
            "ticker": "BAC",
            "marketValue": 100
        },
        {
            "ticker": "BAC",
            "marketValue": 400
        },
        {
            "ticker": "BAC",
            "marketValue": 200
        }
    ]
}

The result I want (where "group-weight" equals each item's marketValue divided by the sum of the same-ticker marketValues:

{
    "positions": [
        {
            "ticker": "AAPL",
            "marketValue": 100,
            "group-weight": 0.3333
        },
        {
            "ticker": "AAPL",
            "marketValue": 200,
            "group-weight": 0.6667
        },
        {
            "ticker": "ATVI",
            "marketValue": 200,
            "group-weight": 0.4
        },
        {
            "ticker": "ATVI",
            "marketValue": 300,
            "group-weight": 0.6
        },
        {
            "ticker": "BAC",
            "marketValue": 100,
            "group-weight": 0.1429
        },
        {
            "ticker": "BAC",
            "marketValue": 400,
            "group-weight": 0.5714
        },
        {
            "ticker": "BAC",
            "marketValue": 200,
            "group-weight": 0.2857
        }
    ]
}

I can get the sum of the groups using:

positions{`ticker`: $sum(marketValue)}

but can't get that next step where I divide by the group sums.

https://try.jsonata.org/m_xPDfncW

mercessa
  • 27
  • 2

1 Answers1

0
(
    $totals := positions{ticker: $sum(marketValue)};
    positions.{
        "marketValue": marketValue,
        "ticker": ticker,
        "group-weight": marketValue / $lookup($totals, ticker)
    }
)

See https://try.jsonata.org/jLDI1Pgnx

Andrew Coleman
  • 1,331
  • 8
  • 8