Extract Country

[joaoftrodrigues]

Description

Must extract country from Location. After that, the extractions made on location, will be adapted to use on Born.

Regular Expressions

So far, the best expression found, was:

db.atpplayers.aggregate([{ $addFields: {Country: { $regexFind: { input: "$Location", regex: "([a-zA-Z ]*,[a-zA-Z ]*,([ a-zA-Z.]*))|([\w ]*,([ a-zA-Z.]*))" }}}}])

Results

City + Country

City + State + Country

Was thought to have a problem, but it's possible to reach 3rd position on captures array.

Some considerations

Follow rules would be preferable, instead the ones being used:

• \w -> "a-zA-Z"
• • -> * (for words)
• [] -> ()

But the results didn't reach what was intended, even though, by logic it should. That way, we have more matches than the ones needed.

[joaoftrodrigues]

Queries

Match regular expression in location and store it on db

db.atpplayers.aggregate([{ $addFields: {Country: { $regexFind: { input: "$Location", regex: "([ ]*[a-zA-Z ]*,[ ]?[a-zA-Z ]*,[ ]*([ a-zA-Z.]*))|([\w ]*,[ ]*([ a-zA-Z.]*))" }}}}])

Change countries' values to matched words

Slice makes possible to access an element based on its index, but it returns an array, so $first must be used to get only the value. db.atpplayers.updateMany({}, [{$set: {Country: {$first: {$slice: ["$Country.captures",-1,1]}}}}])

[joaoftrodrigues]

Correct Queries

Previous queries were having country fields with null, this is a fix to the problem.

Match regular expression in location and store it on db

db.atpplayers.aggregate([{ $addFields: {Country: { $regexFind: { input: "$Location", regex: "([ ]*[a-zA-Z ]*,[ ]?[a-zA-Z ]*,[ ]*([ a-zA-Z.]*))|([\w ]*,[ ]*([ a-zA-Z.]*))" }}}}, {$out: {db: "atp", coll: "atpplayers"}}])

Remove nulls from array

This must be done, as fields with state will have 2 nulls on different indexes, from fields without state. This way, the index will be the same for both cases.

• Associate Country to captures array, to posterior use of $pull (not possible to access subfields using it)

db.atpplayers.updateMany({}, [{$set: {Country: "$Country.captures"}}])

• Remove Nulls

db.atpplayers.updateMany({}, {$pull: {Country: null}})

Change countries' values to matched words

Slice makes possible to access an element based on its index, but it returns an array, so $first must be used to get only the value. db.atpplayers.updateMany({}, [{$set: {Country: {$first: {$slice: ["$Country",-1,1]}}}}])