Question 109040 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json document search with ability to group them by a field and return only the documents with the Maximum value of some other field within each group

I want to find the best way to perform a server-side query to find JSON documents by some initial query criteria, then grouping all these documents by some field in the document and then reducing each group to only contain a single document that contained the highest integer value of another field.

Currently, we are using the results from the jsonPropertyValueQuery, then doing the filtering Java side. This is not optimal as some of our queries can return hundreds of large documents and we would prefer to avoid the data transfer overhead.

Lets say we have the below JSON documents found from the query cts.jsonPropertyValueQuery("typeId", "CAR"):

Document 1

{
  "id": "aa1234",
  "typeId": "CAR",
  "version": 1
}

Document 2

{
  "id": "aa1234",
  "typeId": "CAR",
  "version": 2
}

Document 3

{
  "id": "cc1234",
  "typeId": "CAR",
  "version": 1
}

I want a way to filter/reduce these documents server side (not console side) that allow us to return only Document 2 and Document 3, since out of all the documents with the id=aa1234, Document 2 has the highest(max) version, and out of all documents with the id=cc1234, Document 3 has the highest(max) version.

If there is a way to do this from the Java API directly that would be ideal, but after researching it is looking like we are going to have to send a JavaScript/XQuery block using ServerEvaluationCall.

I have looked into using the JSearch reduce function, but without the ability to group them by the id first, it will only return the single document that contains the highest(max) version. In the above example it only returns Document 2, which is not what we need. Therefore, as stated above we have resorted to just filtering the group of documents Java side.

Tags:

2

Answers


  1. 0

    You could iterate over the results of the search, and capture the doc that has the highest version for each id in an object and then return the values as an Array, or convert into a Sequence:

    const maxVersionByID = { };
for (const doc of cts.search(cts.jsonPropertyValueQuery("typeId", "CAR")) ) {
  const obj = doc.toObject();
  const previousVersion = maxVersionByID[obj.id] ? parseInt(maxVersionByID[obj.id].version) : -1
  if (previousVersion < parseInt(obj.version)) {
    maxVersionByID[obj.id] = obj;
  }
}
Sequence.from(Object.values(maxVersionByID))
    Login or Signup to reply.
  2. 0

    A solution using a JavaScript Map and Array.reduce():

    const searchResults = cts.search(cts.jsonPropertyValueQuery("typeId", "CAR")).toArray();
const maxVersionByID = searchResults.reduce(
    (entryMap, e) => { 
      const obj = e.toObject();
      const previousVersion = entryMap.get(obj.id) ? entryMap.get(obj.id).version : -1
      if (previousVersion < parseInt(obj.version)) {
         entryMap.set(obj.id, e) 
      }
      return entryMap;
    },
    new Map()
);
Array.from(maxVersionByID.values())
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search