If you had a range index on those two fields, then you could do this very easily with cts.valueCoOcurrences().
Returns value co-occurrences (that is, pairs of values, both of which appear in the same fragment) from the specified value lexicon(s). The values are returned as an ArrayNode with two children, each child containing one of the co-occurring values. You can use cts.frequency on each item returned to find how many times the pair occurs. Value lexicons are implemented using range indexes; consequently this function requires a range index for each input index reference. If an index or lexicon is not configured for any of the input references, an exception is thrown.
For example, with path-range-indexes on the /name and /location/state/code then the query would look like this:
cts.valueCoOccurrences(cts.pathReference("/name"), cts.pathReference("/location/state/code"))
Without indexes, then the brute force method that reads all of the documents would look something like this:
const nameAndCode = new Set();
for (const doc of cts.search(cts.andQuery([cts.jsonPropertyScopeQuery("name", cts.trueQuery()),
cts.jsonPropertyScopeQuery("code", cts.trueQuery())])) ) {
const obj = doc.toObject()
nameAndCode.add(obj.name+","+obj.location.state.code);
}
Array.from(nameAndCode)
But it might be slow, and you run the risk of blowing an Expanded Tree Cache error if all of the docs can't be read at once.
You could also do some sort of iterative search to sample some documents, add their values to the set, and then use the accumulated values to exclude docs in the next search until it no longer returns any documents or hits some limit of number of searches.
