source: other-projects/maori-lang-detection/src/org/greenstone/atea/MongoDBQueryer.java@ 33913

package org.greenstone.atea;

//import org.bson.BSONObject;

import com.mongodb.client.AggregateIterable;
import com.mongodb.client.MongoCollection; 

// to use collection.find() filters like eq(), regex() etc
import static com.mongodb.client.model.Filters.*;
// to use collection.find().projection() filters like include() etc
import static com.mongodb.client.model.Projections.*;
// to use aggregation functions like unwind(), match(), sort() etc
import static com.mongodb.client.model.Aggregates.*;
// to use functions like sum() and addToSet() within aggregation functions
import static com.mongodb.client.model.Accumulators.*;

//import org.bson.conversions.Bson;
import com.mongodb.BasicDBObject;


import com.mongodb.Block;

import org.bson.BsonArray;
import org.bson.BsonString;
import org.bson.BsonValue;
import org.bson.Document;
import org.bson.conversions.Bson;
import org.bson.json.JsonMode;
import org.bson.json.JsonWriterSettings;
    
import com.mongodb.util.JSON;
//import com.mongodb.DBObject;


import com.google.gson.*; // for pretty printing

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.UncheckedIOException;
import java.io.Writer;
import javax.xml.ws.Holder;


import java.util.Arrays;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeSet;


import org.apache.log4j.Logger;
import org.apache.commons.csv.*;

/** 
 * https://www.tutorialspoint.com/mongodb/mongodb_java.htm
 *
 * TO COMPILE:
 *    maori-lang-detection/src$
 *       javac -cp ".:../conf:../lib/*" org/greenstone/atea/MongoDBQueryer.java
 *
 * TO RUN:
 *       java -cp ".:../conf:../lib/*" org.greenstone.atea.MongoDBQueryer
 *
 * Manually connecting to mongodb from client:
 *    mongo 'mongodb://mongodb.cms.waikato.ac.nz:27017' -u USERNAME -p
 * Then after connecting with pwd, type:
 *    use DBNAME
 *
 * Or connect to mongodb and specify db in one statement:
 *    mongo 'mongodb://mongodb.cms.waikato.ac.nz:27017/DBNAME?authSource=admin' -u USERNAME -p
 *
 * Some links:
 *   - https://stackoverflow.com/questions/19938153/do-i-need-to-explicitly-close-connection
 *   - https://docs.mongodb.com/manual/reference/glossary/ (particularly "collection")
 *   - https://tecadmin.net/tutorial/mongodb/drop-collection/
 * IMPORTANT LINK:
 *   - https://www.mongodb.com/blog/post/6-rules-of-thumb-for-mongodb-schema-design-part-1
 *
 * API:
 * - https://mongodb.github.io/mongo-java-driver/3.4/javadoc/?com/mongodb/client/MongoCollection.html#find--
 * - examples: https://mongodb.github.io/mongo-java-driver/3.4/driver/getting-started/quick-start/
 */
public class MongoDBQueryer extends MongoDBAccess {

    private static Logger logger = Logger.getLogger(org.greenstone.atea.MongoDBQueryer.class.getName());

    public static final String NEWLINE = System.getProperty("line.separator");
    
    /** mongodb filter types to execute */
    public static final int IS_MRI = 0;
    public static final int CONTAINS_MRI = 1;

    /** Some reused fieldnames in the Websites collection */
    private static final String FILTER_NUMPAGES_IN_MRI = "numPagesInMRI";
    private static final String FILTER_NUMPAGES_CONTAINING_MRI = "numPagesContainingMRI";


    
    public MongoDBQueryer() throws Exception {
    super();
    }

    
    public ArrayList<String> queryAllMatchingIsMRIURLs(String domain) {
    return queryAllMatchingURLsFilteredBy(domain, IS_MRI);
    }
    public ArrayList<String> queryAllMatchingcontainsMRIURLs(String domain) {
    return queryAllMatchingURLsFilteredBy(domain, CONTAINS_MRI);
    }
    
    /** 
     * Java mongodb find: https://mongodb.github.io/mongo-java-driver/3.4/driver/getting-started/quick-start/ 
     * Java mongodb find filters: https://mongodb.github.io/mongo-java-driver/3.4/javadoc/?com/mongodb/client/model/Filters.html
     * Java mongodb projection: https://stackoverflow.com/questions/44894497/retrieving-data-with-mongodb-java-driver-3-4-using-find-method-with-projection
     * mongodb projection: https://docs.mongodb.com/v3.2/reference/method/db.collection.find/#db.collection.find
     *
     * Parse MongoDB query into Java: https://stackoverflow.com/questions/17326747/parsing-strings-to-mongodb-query-documents-with-operators-in-java
     * Maybe also https://stackoverflow.com/questions/48000891/parse-mongodb-json-query-in-java-with-multiple-criteria
     * https://stackoverflow.com/questions/55029222/parse-mongodb-query-to-java
     * http://pingax.com/trick-convert-mongo-shell-query-equivalent-java-objects/
     */
    public ArrayList<String> queryAllMatchingURLsFilteredBy(String domain, int filterType) {
    
    final ArrayList<String> urlsList = new ArrayList<String>();
    
    // remove any http(s)://(www.) from the start of URL first
    // since it goes into a regex
    domain = Utility.stripProtocolAndWWWFromURL(domain);

    // load the "webpages" db table
    // in mongodb, the equivalent of db tables are called 'collections' 
    MongoCollection<Document> collection = getWebpagesCollection();

    // code we'll execute in Iterable.forEach() below
    // see also https://www.baeldung.com/foreach-java
    Block<Document> storeURL = new Block<Document>() {
        @Override
        public void apply(final Document document) {
            //System.out.println(document.toJson());
            String url = document.getString("URL");
            // add to our urlsList
            //System.out.println(url);
            urlsList.add(url);
        }
        };

    
    // Run the following mongodb query:
    //    db.getCollection('Webpages').find({URL: /domain/, isMRI: true}, {URL: 1, _id: 0})
    
    // 1. One way that works:
    //collection.find(and(eq("isMRI", true), regex("URL", pattern))).projection(fields(include("URL"), excludeId())).forEach(storeURL);

    // 2. Another way:
    //String query = "{URL: /DOMAIN/, isMRI: true}";
    String query = "{URL: /DOMAIN/, ";
    if(filterType == IS_MRI) {
        query += "isMRI: true}";
    } else if(filterType == CONTAINS_MRI) {
        query += "containsMRI: true}";
    }
    
    domain = domain.replace(".", "\\."); // escape dots in domain for regex
    query = query.replace("DOMAIN", domain);

    //System.err.println("Executing find query: " + query);
    
    BasicDBObject findObj = BasicDBObject.parse(query);
    BasicDBObject projectionObj = BasicDBObject.parse("{URL: 1, _id: 0}");
    

    collection.find(findObj).projection(projectionObj).forEach(storeURL);
    
    return urlsList;
    }

    /**     
     * RUNNING A MONGODB COLLECTION.AGGREGATE() in JAVA:
     *
     * https://stackoverflow.com/questions/31643109/mongodb-aggregation-with-java-driver
     * https://stackoverflow.com/questions/48000891/parse-mongodb-json-query-in-java-with-multiple-criteria
     * Not Java: https://stackoverflow.com/questions/39060221/a-pipeline-stage-specification-object-must-contain-exactly-one-field-with-php-mo
     *
     * (https://stackoverflow.com/questions/55029222/parse-mongodb-query-to-java)
     * https://www.programcreek.com/java-api-examples/?api=com.mongodb.client.model.Aggregates
     * On using group(TExpression) inside collection.aggregate().
     *
     *  For forEach lamba expressions, see also https://www.baeldung.com/foreach-java
     *  and https://www.javatpoint.com/java-8-foreach
     *  and https://stackoverflow.com/questions/47979978/ambiguous-reference-to-foreach-when-listing-mongodbs-database-in-java
     *
     * Count of NZ (incl .nz TLD)  websites containing a positive number of sentences in MRI, 
     * listing all the base domain strings (unsorted with protocol and any www)
     * and total counts of numPagesInMRI and numPagesContainingMRI across all these
     * matching sites.
     * 
     * The mongodb aggregate() we want to run this time:
     *
       db.Websites.aggregate([
       {
        $match: {
            $and: [
                {numPagesContainingMRI: {$gt: 0}},
                {$or: [{geoLocationCountryCode:"NZ"},{domain: /\.nz/}]}
            ]
          }
    },
    { $unwind: "$geoLocationCountryCode" },
    {
          $group: {
            _id: "nz",
            count: { $sum: 1 },            
        domain: { $addToSet: '$domain' }
          }
    },
    { $sort : { count : -1} }
    ]);
    */
    public void aggregateContainsMRIForNZ(Writer writer, int filterType) throws IOException {
    // working with the WebSites collection, not WebPages collection!
    MongoCollection<Document> collection = getWebsitesCollection();

    String mriFilterString = (filterType == CONTAINS_MRI) ? "{numPagesContainingMRI: {$gt: 0}}" : "{numPagesInMRI: {$gt: 0}}";

    // Want a counter,
    // but lambda expressions can only take final variables and those can't be incremented
    // But can use an array to store incrementable counter or Holder type
    // https://stackoverflow.com/questions/28790784/java-8-preferred-way-to-count-iterations-of-a-lambda
    Holder<Integer> docNum = new Holder<>(0);
    
    Bson orQuery = or(
              BasicDBObject.parse("{geoLocationCountryCode: \"NZ\"}"),
              BasicDBObject.parse("{domain: /\\.nz$/}")
              );
    Bson andQuery = and(
        BasicDBObject.parse(mriFilterString),
        orQuery);
    
    // Hopefully the lambda expression (forEach()) at end means
    // we write out each result Document as we get it
    collection.aggregate(Arrays.asList(
         match(andQuery),
         unwind("$geoLocationCountryCode"),
         group("NZ", Arrays.asList(sum("count", 1),
                   addToSet("domain", "$domain"))),
         sort(BasicDBObject.parse("{count : -1}"))
       )).forEach((Block<Document>)doc -> writeDoc(docNum.value++, doc, writer));

    // should only have one doc for NZ since it's a count by geolocation.

    return;
    }

        
    /**
     * Count of overseas (non-NZ and non-.nz TLD) websites
     * containing a positive number of sentences in MRI, 
     * listing all the base domain strings (unsorted with protocol and any www)
     * and total counts of numPagesInMRI and numPagesContainingMRI across all these
     * matching sites. Regardless of whether there's an mi in the URL path of any or not.
     *
     * The aggregate() we want to run this time:
     *
       db.Websites.aggregate([
       {
         $match: {
            $and: [
                {geoLocationCountryCode: {$ne: "NZ"}},
                {domain: {$not: /\.nz/}},
                {numPagesContainingMRI: {$gt: 0}}
            ]
      }
    },
    { $unwind: "$geoLocationCountryCode" },
    {
          $group: {
            _id: {$toLower: '$geoLocationCountryCode'},
            count: { $sum: 1 },
        domain: { $addToSet: '$domain' }
          }
     },
     { $sort : { count : -1} }
    ]);
    */
    public void aggregateContainsMRIForOverseas(Writer writer, int filterType)
    throws UncheckedIOException {
        // working with the WebSites collection, not WebPages collection!
    MongoCollection<Document> collection = getWebsitesCollection();
    
    String mriFilterString = (filterType == CONTAINS_MRI) ? "{numPagesContainingMRI: {$gt: 0}}" : "{numPagesInMRI: {$gt: 0}}";
    
    // Want a counter,
    // but lambda expressions can only take final variables and those can't be incremented
    // But can use an array to store incrementable counter or Holder
    // https://stackoverflow.com/questions/28790784/java-8-preferred-way-to-count-iterations-of-a-lambda
    Holder<Integer> docNum = new Holder<>(1);
    
    Bson andQuery = and(
        BasicDBObject.parse("{geoLocationCountryCode: {$ne: \"NZ\"}}"),
        BasicDBObject.parse("{domain: {$not: /\\.nz$/}}"),
        BasicDBObject.parse(mriFilterString));

    collection.aggregate(Arrays.asList(
         match(andQuery),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(sum("count", 1), 
                        addToSet("domain", "$domain"))),
         sort(BasicDBObject.parse("{count : -1}"))
       )).forEach((Block<Document>)doc -> writeDoc(docNum.value++, doc, writer));

    return;
    }


    /** Count by country code of overseas (non-NZ and non-nz TLD) websites
     * containing a positive number of sentences in MRI,
     * listing all the base domain strings (unordered and with protocol and any www)
     * and total counts of numPagesInMRI and numPagesContainingMRI across all these
     * matching sites.
     *
     * The aggregate() we want to run this time:
     *
       db.Websites.aggregate([
       {
         $match: {
            $and: [
                {geoLocationCountryCode: {$ne: "NZ"}},
                {domain: {$not: /\.nz/}},
                {numPagesContainingMRI: {$gt: 0}},
                {$or: [{geoLocationCountryCode: "AU"}, {urlContainsLangCodeInPath: false}]}            
            ]
      }
    },
    { $unwind: "$geoLocationCountryCode" },
    {
          $group: {
            _id: {$toLower: '$geoLocationCountryCode'},
            count: { $sum: 1 },
        domain: { $addToSet: '$domain' }
          }
     },
     { $sort : { count : -1} }
    ]);
    */
    public void aggregateContainsMRIForOverseas(Writer writer, int filterType,
                            boolean isMiInURLPath) throws UncheckedIOException
    {
    // working with the WebSites collection, not WebPages collection!
    MongoCollection<Document> collection = getWebsitesCollection();
    
    String mriFilterString = (filterType == CONTAINS_MRI) ? "{numPagesContainingMRI: {$gt: 0}}" : "{numPagesInMRI: {$gt: 0}}";

    // Want a counter,
    // but lambda expressions can only take final variables and those can't be incremented
    // But can use an array to store incrementable counter or Holder
    // https://stackoverflow.com/questions/28790784/java-8-preferred-way-to-count-iterations-of-a-lambda
    Holder<Integer> docNum = new Holder<>(1);

    /*
    Bson orQuery = or(
              BasicDBObject.parse("{geoLocationCountryCode: \"AU\"}"),
              BasicDBObject.parse("{urlContainsLangCodeInPath: "+ isMiInURLPath +"}")
              // e.g. "{urlContainsLangCodeInPath: false}"
              );
    */
    Bson andQuery = and(
        BasicDBObject.parse(mriFilterString),
        BasicDBObject.parse("{geoLocationCountryCode: {$ne: \"NZ\"}}"),
        BasicDBObject.parse("{domain: {$not: /\\.nz$/}}"),      
        BasicDBObject.parse("{urlContainsLangCodeInPath: "+ isMiInURLPath +"}"));//orQuery);

    collection.aggregate(Arrays.asList(
         match(andQuery),  //match(BasicDBObject.parse(matchQuery))
         // match((List<DBObject>)JSON.parse(matchQuery)),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(sum("count", 1), 
                        addToSet("domain", "$domain"))),
         sort(BasicDBObject.parse("{count : -1}"))
       )).forEach((Block<Document>)doc -> writeDoc(docNum.value++, doc, writer));

    // casting to Block<Document> necessary because otherwise we see the error at
    // https://stackoverflow.com/questions/47979978/ambiguous-reference-to-foreach-when-listing-mongodbs-database-in-java

    // Less efficient way is to keep all the results in memory and then
    // write them out one at a time
    /*
    AggregateIterable<Document> output
        = collection.aggregate(Arrays.asList(
         match(andQuery),  //match(BasicDBObject.parse(matchQuery))
         // match((List<DBObject>)JSON.parse(matchQuery)),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(sum("count", 1), addToSet("domain", "$domain"))),
         sort(BasicDBObject.parse("{count : -1}"))
     ));

    
    for (Document doc : output) {
        //System.out.println(doc);
        System.out.println(doc.toJson());
        
    }
    */
    return;
    }

    /** Perform the aggregates for writing out the summary tables. */
    public void writeTables(File outFolder) {
    // In this function, we're always dealing with the Websites mongodb collection.
    MongoCollection<Document> collection = getWebsitesCollection();

    String[] tableNames = { "", "1table_allCrawledSites", "2table_sitesWithPagesInMRI",
    "3table_sitesWithPagesContainingMRI", "4table_containsMRI_exclTentativeProductSites",
    "5table_sitesWithPagesContainingMRI_allNZGrouped"
    };
    for (int tableNum = 1; tableNum < tableNames.length; tableNum++) {
        File outFile = new File(outFolder, tableNames[tableNum] + ".json");
        File csvFile = new File(outFolder, tableNames[tableNum] + ".csv");
        try (
         Writer writer = new BufferedWriter(new FileWriter(outFile));
         CSVPrinter csvWriter = new CSVPrinter(new FileWriter(csvFile), CSVFormat.DEFAULT.withQuoteMode(QuoteMode.MINIMAL)); // quote ALL vs MINIMAL vs NON_NUMERIC fields
         ) {

        // Write out the CSV column headings
        // https://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVPrinter.html
        csvWriter.printRecord("countryCode", "siteCount",
              "numPagesInMRI count","numPagesContainingMRICount",
              "totalPagesAcrossMatchingSites"/*, "domain"*/);
        
        AggregateIterable<Document> output = getTable(collection, tableNum); //doTable1().forEach((Block<Document>)doc -> writeDoc(doc, writer));
        

        int docNum = 0;
        // get any NZ specific row's data, if it exists for this table
        // and add that as the docNum="0th" doc
        Document nzDoc = getNZTableRowData(collection, tableNum);
        if(nzDoc != null) {
            writeDocAsJsonRecord(docNum, nzDoc, writer);
            writeDocAsCSVRecord(docNum, nzDoc, csvWriter); 
        }
        // all other table row data start at 1 for docNum
        for (Document doc : output) {           
            //System.out.println(doc);
            writeDocAsJsonRecord(++docNum, doc, writer);
            writeDocAsCSVRecord(++docNum, doc, csvWriter);
        }       
        
        logger.info("@@@ Wrote out table into file: " + Utility.getFilePath(outFile) + " and .csv");
        } catch(UncheckedIOException ioe) {
        logger.error("Caught UncheckedIOException: " + ioe.getMessage(), ioe);
        }
        catch(Exception e) {
        logger.error("Could not write table to file " + outFile + " or .csv equivalent" , e);
        }
    }
    }

    public Document getNZTableRowData(MongoCollection<Document> collection, int tableNum) {
    
    Document nzRowData = null;
    switch(tableNum) {
    case 1: case 2: case 3: case 4:
        
        break;

    case 5:
        /* Get NZ only table data:
           db.Websites.aggregate([
           {
           $match: {
           $and: [
           {numPagesContainingMRI: {$gt: 0}},
           {$or: [{geoLocationCountryCode:"NZ"},{domain: /\.nz$/}]}
           ]
           }
           },
           { $unwind: "$geoLocationCountryCode" },
           {
           $group: {
           _id: "NZ",
            count: { $sum: 1 },
            //domain: { $addToSet: '$domain' },
        numPagesInMRICount: { $sum: '$numPagesInMRI' },
        numPagesContainingMRICount: { $sum: '$numPagesContainingMRI' },
        totalPagesAcrossMatchingSites: { $sum: '$totalPages'}
        }
        },
        { $sort : { count : -1} }
        ]);

        */
        Bson orQuery = or(
              BasicDBObject.parse("{geoLocationCountryCode: \"NZ\"}"),
              BasicDBObject.parse("{domain: /\\.nz$/}")
              );
        Bson andQuery = and(
                BasicDBObject.parse("{numPagesContainingMRI: {$gt: 0}}"),
                orQuery
              );
        AggregateIterable<Document> output = collection.aggregate(Arrays.asList(
         match(andQuery),
         unwind("$geoLocationCountryCode"),
         group("NZ", Arrays.asList(
                sum("count", 1),
                /*addToSet("domain", "$domain"),*/
                sum("numPagesInMRICount", "$numPagesInMRI"),
                sum("numPagesContainingMRICount", "$numPagesContainingMRI"),
                sum("totalPagesAcrossMatchingSites", "$totalPages"))),
         sort(BasicDBObject.parse("{count : -1}"))       
        ));
        
        nzRowData = output.first(); // first and only document in result
        
        break;

     default: logger.error("Unknown table number: " + tableNum);   
    }

    return nzRowData;   
    }
    
    public AggregateIterable<Document> getTable(MongoCollection<Document> collection, int tableNum)
    {
    
    AggregateIterable<Document> output = null;
    Bson orQuery = null;
    Bson andQuery = null;
    
    switch(tableNum) {
        
    case 1:
        /* 1table_allCrawledSites -

           db.Websites.aggregate([       
           { $unwind: "$geoLocationCountryCode" },
           {
           $group: {
           _id: "$geoLocationCountryCode",
           count: { $sum: 1 },
           //domain: { $addToSet: '$domain' },
           numPagesInMRICount: { $sum: '$numPagesInMRI' },
           numPagesContainingMRICount: { $sum: '$numPagesContainingMRI' },
           totalPagesAcrossSites: { $sum: '$totalPages'}
           }
           },
           { $sort : { count : -1} }
           ]);
         */
        output = collection.aggregate(Arrays.asList(
         //match(BasicDBObject.parse("{urlContainsLangCodeInPath:true}")),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(
                sum("count", 1),
                /*addToSet("domain", "$domain"),*/
                sum("numPagesInMRICount", "$numPagesInMRI"),
                sum("numPagesContainingMRICount", "$numPagesContainingMRI"),
                sum("totalPagesAcrossMatchingSites", "$totalPages"))),
         sort(BasicDBObject.parse("{count : -1}"))       
        ));
        break;

    case 2:
        /*
          db.Websites.aggregate([
          { $match: { numPagesInMRI: {$gt: 0} } },
          { $unwind: "$geoLocationCountryCode" },
          {
          $group: {
          _id: {$toLower: '$geoLocationCountryCode'}, // ignore toLower: _id:'$geoLocationCountryCode'
          count: { $sum: 1 },
          //domain: { $addToSet: '$domain' },
          numPagesInMRICount: { $sum: '$numPagesInMRI' },
          numPagesContainingMRICount: { $sum: '$numPagesContainingMRI' },
          totalPagesAcrossSitesWithPositiveMRICount: { $sum: '$totalPages'}
          }
          },
          { $sort : { count : -1} }
          ]);
         */
         output = collection.aggregate(Arrays.asList(
         match(BasicDBObject.parse("{ numPagesInMRI: {$gt: 0} }")),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(
                sum("count", 1),
                /*addToSet("domain", "$domain"),*/
                sum("numPagesInMRICount", "$numPagesInMRI"),
                sum("numPagesContainingMRICount", "$numPagesContainingMRI"),
                sum("totalPagesAcrossMatchingSites", "$totalPages"))),
         sort(BasicDBObject.parse("{count : -1}"))       
        ));
        break;

    case 3:
        /*
          db.Websites.aggregate([
          {
          $match: { numPagesContainingMRI: {$gt: 0} }
          },
          { $unwind: "$geoLocationCountryCode" },
          {
          $group: {
          _id: '$geoLocationCountryCode',
          count: { $sum: 1 },
          //domain: { $addToSet: '$domain' },
          numPagesInMRICount: { $sum: '$numPagesInMRI' },
          numPagesContainingMRICount: { $sum: '$numPagesContainingMRI' },
          totalPagesAcrossSitesWithPosContainsMRI: { $sum: '$totalPages'}
          }
          },
          { $sort : { count : -1} }
          ]);
         */
        output = collection.aggregate(Arrays.asList(
         match(BasicDBObject.parse("{ numPagesInMRI: {$gt: 0} }")),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(
                sum("count", 1),
                /*addToSet("domain", "$domain"),*/
                sum("numPagesInMRICount", "$numPagesInMRI"),
                sum("numPagesContainingMRICount", "$numPagesContainingMRI"),
                sum("totalPagesAcrossMatchingSites", "$totalPages"))),
         sort(BasicDBObject.parse("{count : -1}"))       
        ));
        break;

    case 4:
        /*
          db.Websites.aggregate([
          {
          $match: {
          $and: [
          {numPagesContainingMRI: {$gt: 0}},
          {$or: [{geoLocationCountryCode: /(NZ|AU)/}, {domain: /\.nz$/}, {urlContainsLangCodeInPath: false}]}
          ]
          }
          },
          { $unwind: "$geoLocationCountryCode" },
          {
          $group: {
          _id: {$toLower: '$geoLocationCountryCode'},
          count: { $sum: 1 },
          //domain: { $addToSet: '$domain' },
          numPagesInMRICount: { $sum: '$numPagesInMRI' },
          numPagesContainingMRICount: { $sum: '$numPagesContainingMRI' },
          totalPagesAcrossMatchingSites: { $sum: '$totalPages'}
          }
          },
          { $sort : { count : -1} }
          ]);
        */

        orQuery = or(
              BasicDBObject.parse("{geoLocationCountryCode: /(NZ|AU)/}"),
             //BasicDBObject.parse("{geoLocationCountryCode: \"NZ\"}"),
              BasicDBObject.parse("{domain: /\\.nz$/}"),
              BasicDBObject.parse("{urlContainsLangCodeInPath: false}")
              );
        andQuery = and(
        BasicDBObject.parse("{numPagesContainingMRI: {$gt: 0}}"),
        orQuery);
        output = collection.aggregate(Arrays.asList(
         match(andQuery),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(
                sum("count", 1),
                /*addToSet("domain", "$domain"),*/
                sum("numPagesInMRICount", "$numPagesInMRI"),
                sum("numPagesContainingMRICount", "$numPagesContainingMRI"),
                sum("totalPagesAcrossMatchingSites", "$totalPages"))),
         sort(BasicDBObject.parse("{count : -1}"))       
        ));
        break;
        
    case 5:
        /*
          Table of count by countryCode of sites with numPagesContainingMRI > 0
          Just do OVERSEAS here, NZ handled separately

          db.Websites.aggregate([
          {
          $match: {
          $and: [
          {geoLocationCountryCode: {$ne: "NZ"}},
          {domain: {$not: /\.nz$/}},
          {numPagesContainingMRI: {$gt: 0}}
          ]
          }
          },
          { $unwind: "$geoLocationCountryCode" },
          {
          $group: {
          _id: '$geoLocationCountryCode',
          count: { $sum: 1 },
          //domain: { $addToSet: '$domain' },
          numPagesInMRICount: { $sum: '$numPagesInMRI' },
          numPagesContainingMRICount: { $sum: '$numPagesContainingMRI' },
          totalPagesAcrossMatchingSites: { $sum: '$totalPages'}
          }
          },
          { $sort : { count : -1} }
          ]);
        */
        
        andQuery = and(
              BasicDBObject.parse("{geoLocationCountryCode: {$ne: \"NZ\"}}"),
              BasicDBObject.parse("{domain: {$not: /\\.nz$/}}"),
              BasicDBObject.parse("{numPagesContainingMRI: {$gt: 0}}")
              );
        output = collection.aggregate(Arrays.asList(
         match(andQuery),
         unwind("$geoLocationCountryCode"),
         group("$geoLocationCountryCode", Arrays.asList(
                sum("count", 1),
                /*addToSet("domain", "$domain"),*/
                sum("numPagesInMRICount", "$numPagesInMRI"),
                sum("numPagesContainingMRICount", "$numPagesContainingMRI"),
                sum("totalPagesAcrossMatchingSites", "$totalPages"))),
         sort(BasicDBObject.parse("{count : -1}"))       
        ));
        
        break;    
    default: logger.error("Unknown table number: " + tableNum);    
    }

     return output;
    
    }
    
    
    
    /**
     * called by lambda forEach() call on Document objects to write them out to a file.
     * Have to deal with unreported exceptions here that can't be dealt with when doing
     * the actual forEach(). See
     * https://stackoverflow.com/questions/39090292/how-to-cleanly-deal-with-unreported-exception-ioexception-in-stream-foreach
     */    
    public void writeDoc(int docNum, Document doc, Writer writer) throws UncheckedIOException {

    // If there's a domain field in the json Doc, sort this domain listing alphabetically
    Object domainList = doc.remove("domain");
    ///logger.info("CLASS: " + domainList.getClass());
    if(domainList != null) {
        List sortedList = sortAlphabetically(domainList);
        doc.put("uniqueCount", sortedList.size());
        doc.put("domain", sortedList);      
    }
    
    //OLD WAY: writer.write(doc.toJson(new JsonWriterSettings(JsonMode.STRICT, true)) + NEWLINE);
    // Can't control json output to add newlines after each array element,
    // no matter which JsonMode is used.
    
    // https://mongodb.github.io/mongo-java-driver/3.9/javadoc/index.html?org/bson/json/JsonWriterSettings.html
    // Still can't control array element output,
    // but this way uses newer mongo java driver 3.9(.1). Tried its various JsonModes too:
    //JsonWriterSettings writeSettings = new JsonWriterSettings();
    //writeSettings.builder().outputMode(JsonMode.SHELL).indent(true).build();
    //writer.write(doc.toJson(writeSettings) + NEWLINE);

    // Not the JsonWriter of mongodb java driver:
    // https://stackoverflow.com/questions/54746814/jsonwriter-add-a-new-line
    
    // Have to use gson's pretty print to produce a json string that contains
    // newlines after every array element in the json:
    
    String jsonStr = prettyPrintJson(doc.toJson());
    //System.err.println(jsonStr);
    try {
        writer.write("/* " + docNum + " */" + NEWLINE);
        writer.write(jsonStr + NEWLINE + NEWLINE);
    } catch (IOException ex) {
        //throw ex;
        throw new UncheckedIOException(ex);
    }       
    }
    
    public void writeDocAsJsonRecord(int docNum, Document doc, Writer writer) throws UncheckedIOException {
    String jsonStr = prettyPrintJson(doc.toJson());
    //System.err.println(jsonStr);
    try {
        writer.write("/* " + docNum + " */\n" + jsonStr + NEWLINE);
    } catch (IOException ex) {
        //throw ex;
        throw new UncheckedIOException(ex);
    }
    }

    // TODO
    //public void writeDocToJsonAndCSV(int docNum, Document doc, Writer writer, CSVPrinter csvWriter) throws UncheckedIOException {
    public void writeDocAsCSVRecord(int docNum, Document doc, CSVPrinter csvWriter) throws UncheckedIOException {
    String jsonStr = doc.toJson();
    JsonParser parser = new JsonParser();
    JsonElement json = parser.parse(jsonStr);

    JsonObject jsonObj = (JsonObject)json;
    
    String countryCode = jsonObj.get("_id").getAsString();
    int siteCount = jsonObj.get("count").getAsInt();
    int numPagesInMRICount = jsonObj.get("numPagesInMRICount").getAsInt();
    int numPagesContainingMRICount = jsonObj.get("numPagesContainingMRICount").getAsInt();
    int totalPagesAcrossMatchingSites = jsonObj.get("totalPagesAcrossMatchingSites").getAsInt();
    
    //System.err.println(jsonStr);
    try {
        //writer.write("/* " + docNum + " */\n" + prettyPrintJson(jsonStr) + NEWLINE);
        csvWriter.printRecord(countryCode, siteCount, numPagesInMRICount, numPagesContainingMRICount, totalPagesAcrossMatchingSites);
    } catch (IOException ex) {
        //throw ex;
        throw new UncheckedIOException(ex);
    }
    }
    
    public String prettyPrintJson(String jsonStr) {
    Gson gson = new GsonBuilder().setPrettyPrinting().create();
    JsonParser jp = new JsonParser();
    JsonElement je = jp.parse(jsonStr);
    String prettyJsonString = gson.toJson(je);
    return prettyJsonString;
    }

    private List sortAlphabetically(Object list) {
    //BsonArray domainList = (BsonArray)list;
    ArrayList<BsonValue> domainList = (ArrayList<BsonValue>)list;
    /*  
    // for(String domain : domainList) {
    for(int i = domainList.size() - 1; i >= 0; i--) {
        BsonString domain = domainList.get(i).asString();
        String domainStr = Utility.stripProtocolAndWWWFromURL(domain.toString());
        domainList.set(i, new BsonString(domainStr));       
    }
    Collections.sort(domainList);
    // still need to get rid of non-unique values...
    */

    TreeSet<String> set = new TreeSet<String>();
    for(int i = domainList.size() - 1; i >= 0; i--) {
        ///BsonValue val = domainList.get(i);
        ///BsonString domain = val.asString();
        //BsonString domain = domainList.get(i).asString();
        //String domainStr = Utility.stripProtocolAndWWWFromURL(domain.toString());
        Object domain = domainList.get(i);
        String domainStr = Utility.stripProtocolAndWWWFromURL(domain.toString());
        set.add(domainStr);
        //domainList.set(i, new BsonString(domainStr));     
    }
    
    domainList = new ArrayList<BsonValue>(); //new BsonArray();
    for(String s : set) {
        domainList.add(new BsonString(s));
    }
    return domainList;
    }

}