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

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

• javascript
• jsonata

2 Answers 2

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/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/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
  }
}

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

var query = `
    (
        $distinct($.DOSSIER)[] ~> $map(function($dossier) {
            {
                "DOSSIER": $dossier,
                "Count": $count($filter($, function($v) { $v.DOSSIER = $dossier })),
                "Total_DIP_ID": $sum($filter($, function($v) { $v.DOSSIER = $dossier }).DIP_ID)
            }
        })
    )
`;

var expression = jsonata(query);
var result = expression.evaluate(mydata);
console.log(result);

Expected result

[
    {
        "DOSSIER": "Private",
        "Count": 2,
        "Total_DIP_ID": 36254
    },
    {
        "DOSSIER": "Public",
        "Count": 3,
        "Total_DIP_ID": 238
    }
]