sql-to-mongo-db-query-converter
Clone or download
Permalink
Failed to load latest commit information.
src clean up ObjectIdFunction class Aug 11, 2018
.gitignore initial commit Sep 11, 2016
LICENSE adding license Aug 9, 2018
README.md ObjectId support Aug 10, 2018
README.txt git push tags Feb 20, 2017
pom.xml preparing for development version 1.7 Jul 25, 2018

README.md

sql-to-mongo-db-query-converter

sql-to-mongo-db-query-converter helps you build quieres for MongoDb based on Queries provided in SQL.

Maven

Add a dependency to com.github.vincentrussell:sql-to-mongo-db-query-converter.

<dependency>
   <groupId>com.github.vincentrussell</groupId>
   <artifactId>sql-to-mongo-db-query-converter</artifactId>
   <version>1.6</version>
</dependency>

Requirements

  • JDK 1.7 or higher

Running it from Java

QueryConverter queryConverter = new QueryConverter("select column1 from my_table where value NOT IN ("theValue1","theValue2","theValue3")");
MongoDBQueryHolder mongoDBQueryHolder = queryConverter.getMongoQuery();
String collection = mongoDBQueryHolder.getCollection();
Document query = mongoDBQueryHolder.getQuery();
Document projection = mongoDBQueryHolder.getProjection();
Document sort = mongoDBQueryHolder.getSort();

Running it as a standalone jar

java -jar sql-to-mongo-db-query-converter-1.6-standalone.jar -s sql.file -d destination.json

Options

usage: com.github.vincentrussell.query.mongodb.sql.converter.Main [-s
       <arg> | -sql <arg> | -i]   [-d <arg> | -h <arg>]  [-db <arg>] [-a
       <arg>] [-u <arg>] [-p <arg>] [-b <arg>]
 -s,--sourceFile <arg>        the source file.
 -sql,--sql <arg>             the select statement
 -i,--interactiveMode         interactive mode
 -d,--destinationFile <arg>   the destination file.  Defaults to
                              System.out
 -h,--host <arg>              hosts and ports in the following format
                              (host:port) default port is 27017
 -db,--database <arg>         mongo database
 -a,--auth database <arg>     auth mongo database
 -u,--username <arg>          usename
 -p,--password <arg>          password
 -b,--batchSize <arg>         batch size for query results

Special Aggregation-specific System Properties

-DaggregationAllowDiskUse
Enables writing to temporary files. When set to true, aggregation operations can write data to the _tmp subdirectory in the dbPath directory.

-DaggregationBatchSize
To specify an initial batch size for the cursor

Interactive mode

java -jar target/sql-to-mongo-db-query-converter-1.6-standalone.jar -i
Enter input sql:


select object.key1, object2.key3, object1.key4 from my_collection where object.key2 = 34 AND object2.key4 > 5


******Result:*********

db.my_collection.find({
  "$and": [
    {
      "key2": {
        "$numberLong": "34"
      }
    },
    {
      "object2.key4": {
        "$gt": {
          "$numberLong": "5"
        }
      }
    }
  ]
} , {
  "_id": 0,
  "object.key1": 1,
  "object2.key3": 1,
  "object1.key4": 1
})

##Available options

###Dates

select * from my_table where date(column,'YYY-MM-DD') >= '2016-12-12'


******Result:*********

db.my_table.find({
  "column": {
    "$gte": {
      "$date": 1452556800000
    }
  }
})

###Natural Language Dates

select * from my_table where date(column,'natural') >= '5000 days ago'


******Result:*********

db.my_table.find({
  "column": {
    "$gte": {
      "$date": 1041700019654
    }
  }
})

###Regex

select * from my_table where regexMatch(column,'^[ae"gaf]+$') = true


******Result:*********

db.my_table.find({
  "column": {
    "$regex": "^[ae\"gaf]+$"
  }
})

###Distinct

select distinct column1 from my_table where value IS NULL


******Result:*********

db.my_table.distinct("column1" , {
  "value": {
    "$exists": false
  }
})

###Like

select * from my_table where value LIKE 'start%'


******Result:*********

db.my_table.find({
  "value": {
    "$regex": "^start.*$"
  }
})

###In

select column1 from my_table where value IN ("theValue1","theValue2","theValue3")


******Result:*********

db.my_table.find({ 
	"value" : { 
		"$in" : ["theValue1","theValue2", "theValue3"] 
		}
})

###Not In

select column1 from my_table where value NOT IN ("theValue1","theValue2","theValue3")


******Result:*********

db.my_table.find({ 
	"value" : { 
		"$nin" : ["theValue1","theValue2", "theValue3"] 
		}
})

###Is True

select column1 from my_table where column = true


******Result:*********

db.my_table.find({ 
	"column" : true
})

###Is False

select column1 from my_table where column = false


******Result:*********

db.my_table.find({ 
	"column" : false
})

###Not True

select column1 from my_table where NOT column


******Result:*********

db.my_table.find({ 
	"value" : {$ne: true}
})

###ObjectId Support

select column1 from  where OBJECTID('_id') IN ('53102b43bf1044ed8b0ba36b', '54651022bffebc03098b4568')

******Result:*********

db.my_table.find({ 
	"_id" : {$in: [{$oid: "53102b43bf1044ed8b0ba36b"},{$oid: "54651022bffebc03098b4568"}]}
})
select column1 from  where OBJECTID('_id') = '53102b43bf1044ed8b0ba36b'

******Result:*********

db.my_table.find({ 
	"_id" : {$oid: "53102b43bf1044ed8b0ba36b"}
})

###Delete

delete from my_table where value IN ("theValue1","theValue2","theValue3")


******Result:*********

3 (number or records deleted)

###Group By (Aggregation)

select borough, cuisine, count(*) from my_collection WHERE borough LIKE 'Queens%' GROUP BY borough, cuisine ORDER BY count(*) DESC;


******Mongo Query:*********

db.my_collection.aggregate([{
  "$match": {
    "borough": {
      "$regex": "^Queens.*$"
    }
  }
},{
  "$group": {
    "_id": {
      "borough": "$borough",
      "cuisine": "$cuisine"
    },
    "count": {
      "$sum": 1
    }
  }
},{
  "$sort": {
    "count": -1
  }
}])

###Direct Mongo Integration

You can run the queries against an actual mongodb database and take a look at the results. The default return batch size is 50.

java -jar target/sql-to-mongo-db-query-converter-1.6-SNAPSHOT-standalone.jar -i -h localhost:3086 -db local -b 5
Enter input sql:


select borough, cuisine, count(*) from my_collection GROUP BY borough, cuisine ORDER BY count(*) DESC;


******Query Results:*********

[{
	"_id" : {
		"borough" : "Manhattan",
		"cuisine" : "American "
	},
	"count" : 3205
},{
	"_id" : {
		"borough" : "Brooklyn",
		"cuisine" : "American "
	},
	"count" : 1273
},{
	"_id" : {
		"borough" : "Queens",
		"cuisine" : "American "
	},
	"count" : 1040
},{
	"_id" : {
		"borough" : "Brooklyn",
		"cuisine" : "Chinese"
	},
	"count" : 763
},{
	"_id" : {
		"borough" : "Queens",
		"cuisine" : "Chinese"
	},
	"count" : 728
}]

more results? (y/n): y
[{
	"_id" : {
		"borough" : "Manhattan",
		"cuisine" : "Café/Coffee/Tea"
	},
	"count" : 680
},{
	"_id" : {
		"borough" : "Manhattan",
		"cuisine" : "Italian"
	},
	"count" : 621
},{
	"_id" : {
		"borough" : "Manhattan",
		"cuisine" : "Chinese"
	},
	"count" : 510
},{
	"_id" : {
		"borough" : "Manhattan",
		"cuisine" : "Japanese"
	},
	"count" : 438
},{
	"_id" : {
		"borough" : "Bronx",
		"cuisine" : "American "
	},
	"count" : 411
}]

more results? (y/n): n

Change Log

1.7 (2018-07-24)

Enhancements:

  • Equals, Not Equals, In and Not In ObjectId query support

Change Log

1.6 (2018-07-24)

Bugs:

  • remove double quotes from column names when used in IS NULL query

1.5 (2018-06-15)

Enhancements:

  • upgrade jsqlparser to version 1.2
  • create flatter structure when chaining ORs and ANDs together

1.4 (2018-03-03)

Enhancements:

  • Added support NOT operator on parentheses
  • Added support for delete SQL statements

1.3.4 (2018-01-27)

Enhancements:

  • Added the ability to pass down custom sql functions down to mongo

1.3.2 (2017-07-02)

Enhancements:

  • Added the ability to support queries on boolean fields
  • UTF-8 support

1.3.1 (2017-02-19)

Enhancements:

  • Added the ability to have default type like Number, String, or Date
  • Added the ability to provide a type for each field like Number, String, Date

1.3 (2017-01-31)

Enhancements:

  • Added the ability to provide field types for columns passed into the query via Java API. See QueryConverterTest for examples.

1.2 (2016-11-30)

Bugs:

  • Fix bug with IN and NOT IN expressions from not converting properly to mongo format properly

1.1 (2016-10-05)

Bugs:

  • Fix bug with not being able to parse like queries