Sorry if the title is unclear, but I’m finding my problem hard to explain concisely. I have a number of JSON documents with structure like this:
{
"count": 100,
"groups": [
{
"name": "group A",
"count": 12
},
{
"name": "group B",
"count": 22
},
{
"name": "group C",
"count": 7
}
]
}
Basically, the document has an item count plus a breakdown of that count into smaller groups. So this record represents a collection of 100 items, of which 12 are from group A, 22 are from group B, 7 are from group C.
Now, I have an element range index on “count” and a bunch of such documents. I’d like to be able to sort by any of the options:
- sort by total count (descending or ascending)
- sort by group A count (descending or ascending)
- sort by group B count (descending or ascending)
- sort by group C count (descending or ascending)
I’ve tried
.orderBy(qb.sort("count", "descending"))
This seems to be sort by the total cost (the one in the document root), but I’m not sure if that’s always true or I need to specify something else to guarantee it always gets that particular one.
For sorting by a specific group, I have no idea how to specify it.
Any advice?
Is it possible to store each group in a separate document? That way, you can match and sort groups independently and calculate a grand total for any combination of groups.
As a rule of thumb for those familiar with relational databases, the optimal granularity is to model each row as a separate document.
On MarkLogic 9, you can write a TDE that projects each group as a separate row and then sort rows regardless of document persistence.
If you update groups independently, however, it is still better to write a separate document for each group.
If you are working in MarkLogic 8 and must store multiple groups in a single document, it is possible to use server-side JavaScript or XQuery on the enode to sort the nodes. While that approach works functionally, it doesn’t get the full benefit of the MarkLogic capabilities.
All of the above assumes that you have to materialize these count documents. The other question is whether you can put the things counted in documents, put A, B, and C collections on those countable documents, and calculate the count dynamically for any combination of counable documents.
Hoping that helps,
See the previous answer related to modelling your data or using TDE.
Otherwise, if you have a finite number of groups and that each group existed only once in each document, then you could use a separate path-range-index for each of the groups above and then order by the reference to the appropriate index.