advanced JSON query language

I've explored couple of existing JSON query language such JMESPath, JsonPath and JSONiq. Unfortunately, none of them seem to be able to support my use case in a generic way.

Basically, I'm receiving different type of responses from different web services. I need to give the ability to the user to remap the response in a 2 dimensional array in other to leverage our visualization tool. Based on the new format, the user can decide how to display his data between existing widgets. Pretty much like a customisable dashboard entirely managed on the UI.

Anyway my input looks like:

{
  "category_1": [
    {
      "name": "medium",
      "count": 10
    },
    {
      "name": "high",
      "count": 20
    }
  ],
  "category_2": [
    {
      "name": "medium",
      "count": 30
    },
    {
      "name": "high",
      "count": 40
    }
  ]
}

expected output:

[
  {
    "name": "medium",
    "count": 10,
    "category": "1"
  },
  {
    "name": "high",
    "count": 20,
    "category": "1"
  },
  {
    "name": "medium",
    "count": 30,
    "category": "2"
  },
  {
    "name": "high",
    "count": 40,
    "category": "2"
  }
]

The closer I went is with JMESPath but my query isn't dynamic at all. The user needs to be aware of possible category of grouping.

The query looks like: [ category_1[].{name: name, count: count, category: '1'}, category_2[].{name: name, count: count, category: '2'} ] | []

In other words, I need an enough powerful JSON query language to perform this JavaScript code:

const output = flatMap(input, (value, key) => {
  return value.map(x => {
    return { ...x, category: key };
  });
});

Any thoughts?

This is indeed not currently possible in JMESPath (0.15.x). There are other spec compliant JMESPath packages that (with a bit of extra effort) will do what you require. Using NPM package @metrichor/jmespath (a typescript implementation) you could extend it with the functions you require as follows:

import {
  registerFunction,
  search,
  TYPE_ARRAY,
  TYPE_OBJECT
} from '@metrichor/jmespath';

registerFunction(
  'flatMapValues',
  ([inputObject]) => {
    return Object.entries(inputObject).reduce((flattened, entry) => {
      const [key, value]: [string, any] = entry;

      if (Array.isArray(value)) {
        return [...flattened, ...value.map(v => [key, v])];
      }
      return [...flattened, [key, value]];
    }, [] as any[]);
  },
  [{ types: [TYPE_OBJECT, TYPE_ARRAY] }],
);

With these extended functions a JMESPath expression would now look like this to remap the key into every value:

search("flatMapValues(@)[*].merge([1], {category: [0]})", {
  "category_1": [
    {
      "name": "medium",
      "count": 10
    },
    {
      "name": "high",
      "count": 20
    }
  ],
  "category_2": [
    {
      "name": "medium",
      "count": 30
    },
    {
      "name": "high",
      "count": 40
    }
  ]
});

// OUTPUTS:

[
  {
    category: 'category_1',
    count: 10,
    name: 'medium',
  },
  {
    category: 'category_1',
    count: 20,
    name: 'high',
  },
  {
    category: 'category_2',
    count: 30,
    name: 'medium',
  },
  {
    category: 'category_2',
    count: 40,
    name: 'high',
  },
]

That said you could just register the function you wrote above and use it