Help with JSONata grouping

General
1

This is not necessarily related to node-red, but the JSONata knowledge is here.

I have the following object:

{
	"dates": [{
			"datetime": "2018-06-01 08:14:00"
		},
		{
			"datetime": "2018-06-01 08:16:00"
		},
		{
			"datetime": "2018-06-01 18:14:00"
		},
		{
			"datetime": "2018-06-01 02:14:00"
		},
		{
			"datetime": "2018-06-02 12:14:00"
		},
		{
			"datetime": "2018-06-04 14:14:00"
		}

	]
}

I am trying to get output that produces:
per unique date > count items in groups between a:08:00-17:00, b:17:00-23:00, c:23:00-08:00

I have something like

dates{$substring(datetime,0,10):{"a":{$substring(datetime,11,2):$count(datetime)}}}

which outputs:

{
  "2018-06-01": {
    "a": {
      "18": 1,
      "08": 2,
      "02": 1
    }
  },
  "2018-06-02": {
    "a": {
      "12": 1
    }
  },
  "2018-06-04": {
    "a": {
      "14": 1
    }
  }
}

I am looking for something like:

{
  "2018-06-01": {
    "a": 2, 
    "b": 1,
    "c": 1
    }
  },
  "2018-06-02": {
    "a": 1, 
    "b": 0,
    "c": 0
  }, 
  etc

Is this possible ? I tried to $number($substring(datetime,11,2)) but it does not allow me to parse this

2

I'm no expert at jsonata but it seems to me that the expression will be complex and in 3 months you may have a hard time figuringout what it is. I would look at a different solution to this issue like doing it in a function node.

1 Like
3

I agree. You likely need a map function and that is probably easier done in actual JavaScript with lots of comments.