javascriptjsonata

How group-by and count/sum over JSON data using JSONATA in a web page


Today i spent all hours with this problem: i would group by and count over json data using JSONata.

This is my json data

var mydata = [
{"OWNED": "A","DOSSIER": "Private","DIP_ID": 8619},
{"OWNED": "B","DOSSIER": "Public", "DIP_ID": 17},
{"OWNED": "C","DOSSIER": "Private","DIP_ID": 27635},
{"OWNED": "A","DOSSIER": "Public","DIP_ID": 111},   
{"OWNED": "B","DOSSIER": "Public","DIP_ID": 110}
];

I would obtain a javascript variable with folowing result data:

grouping on OWNED and DOSSIER fields and counting DIP_ID field for each group.

I've used following approach:

var expr = '{"counter": $count(DIP_ID), "sku":$distinct(OWNED)}';

var result = await jsonata(expr).evaluate(mydata);

I can't understand how make an expression to pass to jsonata function.

I've tried to adapt the following examples but i receive always an error from javascript library jsonata.min.js

Probably i can't able to make a string with corrected syntax inside var "expr".

Example Example 2

Maybe the main problem is understand how integrate JSONata query language in my javascript file.

Thanks in advance.

UPDATE:

I'm trying to implement with

$reduce(DOSSIER, function($acc, $item) 
{$merge([$acc, { 
        $item: $count(DOSSIER[$=$item])
     }])
}, {}) ~> $each(function($value, $key) 
{
   { "name": $key, "count": $value }
}
)

and obtain something like this

[
  {
    "name": "Private",
    "count": 2
  },
  {
    "name": "Public",
    "count": 3
  }
]

In this way i'm grouping only on field.

UPDATE 2: In this way i can grouping on two or more fields.

    $${
  OWNED & DOSSIER : {
    "OWNED": [OWNED][0],
    "DOSSIER": [DOSSIER][0],
    "COUNT": $count($)
  }
} ~> $each(function($value) {$value})

Solution

  • How about this expression?

    $.mydata{ 
      OWNED: $.{
        DOSSIER: DOSSIER,
        "DIP_ID": DIP_ID
      }{
        Private: $count($.DIP_ID),
        Public: $count($.DIP_ID)
      }
    }
    

    Should evaluate to:

    {
      "A": {
        "Private": 1,
        "Public": 1
      },
      "B": {
        "Public": 2
      },
      "C": {
        "Private": 1
      }
    }
    

    JSONata Playground: https://jsonatastudio.com/playground/5f80e7bd

    Original answer

    $.mydata{ 
      OWNED: $.{
        DOSSIER: DOSSIER,
        "DIP_ID": DIP_ID
      }{
        Private: $.DIP_ID[],
        Public: $.DIP_ID[],
        "OWNED_GROUP_COUNT": $count($.DIP_ID)
      }
    }
    

    JSONata Playground link: https://jsonatastudio.com/playground/4502c709

    Given a JSON input of:

    {
      "mydata": [
        {
          "OWNED": "A",
          "DOSSIER": "Private",
          "DIP_ID": 8619
        },
        {
          "OWNED": "B",
          "DOSSIER": "Public",
          "DIP_ID": 17
        },
        {
          "OWNED": "C",
          "DOSSIER": "Private",
          "DIP_ID": 27635
        },
        {
          "OWNED": "A",
          "DOSSIER": "Public",
          "DIP_ID": 111
        },
        {
          "OWNED": "B",
          "DOSSIER": "Public",
          "DIP_ID": 110
        }
      ]
    }
    

    It evaluates to:

    {
      "A": {
        "Private": [
          8619
        ],
        "OWNED_GROUP_COUNT": 2,
        "Public": [
          111
        ]
      },
      "B": {
        "Public": [
          17,
          110
        ],
        "OWNED_GROUP_COUNT": 2
      },
      "C": {
        "Private": [
          27635
        ],
        "OWNED_GROUP_COUNT": 1
      }
    }