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".
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})
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
}
}