{"id":87154,"date":"2020-10-22T11:48:39","date_gmt":"2020-10-22T11:48:39","guid":{"rendered":"https:\/\/blog.jetbrains.com\/?post_type=datagrip&#038;p=87154"},"modified":"2021-10-12T15:12:07","modified_gmt":"2021-10-12T14:12:07","slug":"datagrip-2020-3-eap-3-sql-for-mongodb","status":"publish","type":"datagrip","link":"https:\/\/blog.jetbrains.com\/en\/datagrip\/2020\/10\/22\/datagrip-2020-3-eap-3-sql-for-mongodb","title":{"rendered":"DataGrip 2020.3 EAP 3: SQL for MongoDB"},"content":{"rendered":"<p>Hello! In this post about our third <a href=\"https:\/\/www.jetbrains.com\/datagrip\/nextversion\/\" target=\"_blank\" rel=\"noopener\">EAP build for 2020.3<\/a>, we&#8217;ll describe just one feature \u2013 but it\u2019s a huge one:<\/p>\n<h2>SQL for MongoDB<\/h2>\n<p><strong>TL;DR <\/strong>\u2013 You can now use SQL to query MongoDB databases.<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"1870\" height=\"978\" class=\"alignnone size-large wp-image-87205\" src=\"https:\/\/blog.jetbrains.com\/wp-content\/uploads\/2020\/10\/MongDBSQLGeneral.png\"><\/p>\n<p>Only <em>SELECT<\/em> queries work.<\/p>\n<p>Working \u0441lauses: <em>JOIN, <\/em><em>WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET.<\/em><\/p>\n<h3>SELECT<\/h3>\n<ul>\n<li>Options you can use: <em>DISTINCT, ALL.<\/em><\/li>\n<li>You can use aggregate functions:<em> AVG, SUM, MIN, MAX,<\/em> and others.<\/li>\n<li>Columns can be qualified.<\/li>\n<\/ul>\n<p><em>Restrictions<\/em><\/p>\n<ul>\n<li><em>COUNT<\/em> (<em>expression<\/em>) is not yet supported.<\/li>\n<li>You can&#8217;t use aggregate functions, such as <em>AVG, SUM, MIN, <\/em>and<em> MAX,<\/em> as if they were not aggregate. For example,<em> SELECT MAX(1,2,3) <\/em>won&#8217;t work.<\/li>\n<\/ul>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"1270\" height=\"678\" class=\"alignnone size-large wp-image-87185\" src=\"https:\/\/blog.jetbrains.com\/wp-content\/uploads\/2020\/10\/MongDBSQLAvg.png\"><\/p>\n<h3>FROM<\/h3>\n<ul>\n<li>Nested SELECT statements are supported.<\/li>\n<li>You can use aliases for tables.<\/li>\n<li>You must use an alias for a nested SELECT statement. An inspection will display a warning.<\/li>\n<\/ul>\n<p><em>Restriction<\/em><\/p>\n<ul>\n<li>Aliases must not contain dots.<\/li>\n<\/ul>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"1270\" height=\"978\" class=\"alignnone size-large wp-image-87175\" src=\"https:\/\/blog.jetbrains.com\/wp-content\/uploads\/2020\/10\/MongDBSQLSubquery.png\"><\/p>\n<h3>JOIN<\/h3>\n<ul>\n<li>Only <em>JOIN (INNER JOIN)<\/em> and <em>LEFT JOIN (LEFT OUTER JOIN)<\/em> are supported.<\/li>\n<li>Only the <em>ON<\/em> condition is supported. <em>USING<\/em> doesn&#8217;t work.<\/li>\n<li>You can only use one condition at a time.<\/li>\n<li>You can only use the `=` operator in the ON condition. `==` also works.<\/li>\n<\/ul>\n<p><em>Restrictions<\/em><\/p>\n<ul>\n<li>Nested <em>SELECT<\/em>s are not supported in <em>JOIN<\/em> clauses.<\/li>\n<li>Table names and aliases shouldn&#8217;t be duplicated in all JOIN clauses.<\/li>\n<\/ul>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"1270\" height=\"726\" class=\"alignnone size-large wp-image-87165\" src=\"https:\/\/blog.jetbrains.com\/wp-content\/uploads\/2020\/10\/MongDBSQLJoin.png\"><\/p>\n<h3>WHERE<\/h3>\n<ul>\n<li>Supported operands: <em>=, ==, &gt;=, &lt;=, &gt;, &lt;, &lt;&gt;, !=, +, -, \/, *, %, AND, OR, NOT, LIKE, NOT LIKE, IS, IS NOT, IN, NOT IN, BETWEEN, NOT BETWEEN.<\/em><\/li>\n<li><em>LIKE <\/em>requires a string literal. You <strong>can<\/strong> use the classic wildcards for the LIKE operator; they will be translated into the valid regular expression for MongoDB.<\/li>\n<\/ul>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"1270\" height=\"763\" class=\"alignnone size-large wp-image-87195\" src=\"https:\/\/blog.jetbrains.com\/wp-content\/uploads\/2020\/10\/MongDBSQLWhereLike.png\"><\/p>\n<h3>GROUP BY, HAVING, ORDER BY<\/h3>\n<ul>\n<li>You can use aggregate functions:<em> AVG, SUM, MIN, MAX,<\/em> and others.<\/li>\n<\/ul>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"1270\" height=\"895\" class=\"alignnone size-large wp-image-87239\" src=\"https:\/\/blog.jetbrains.com\/wp-content\/uploads\/2020\/10\/MongDBSQLAggregate.png\"><\/p>\n<h3>Data types<\/h3>\n<p>You can use:<\/p>\n<ul>\n<li>String, Integer, Float, Boolean<\/li>\n<li>NULL, NAN, INFINITY<\/li>\n<li>Arrays and maps<\/li>\n<\/ul>\n<h3>Functions<\/h3>\n<p>You can use all available <a href=\"https:\/\/docs.mongodb.com\/manual\/reference\/operator\/aggregation\/\" target=\"_blank\" rel=\"noopener\">MongDB functions<\/a> except <em>map, reduce, filter, <\/em>and<em> let. <\/em><\/p>\n<p>Some functions in MongoDB, such as <a href=\"https:\/\/docs.mongodb.com\/manual\/reference\/operator\/aggregation\/dateToString\/\" target=\"_blank\" rel=\"noopener\">dateToString<\/a>, require <strong>named arguments<\/strong>. To use them, please use a PostgreSQL-like syntax for the named parameters, using &#8216;=&gt;&#8217; or &#8216;:=&#8217;. You&#8217;ll get an error if you don&#8217;t specify names for the parametrized functions, and vice versa.<\/p>\n<h3>Object builders<\/h3>\n<p>The following object builders can all be used: <em>BinData, HexData, UUID, MD5, ObjectID, Date, ISODate, Code, DBRef, RegExp, NumberInt, NumberLong, NumberDecimal, Timestamp, MinKey, MaxKey. <\/em><\/p>\n<p>Regardless of whether you use them with the <em>new<\/em> keyword, we&#8217;ll add it to the actual MongoDB query. The only time we won&#8217;t do so is when you use the Date object builder, because in this case it <a href=\"https:\/\/docs.mongodb.com\/manual\/reference\/method\/Date\/\" target=\"_blank\" rel=\"noopener\">matters<\/a>.<\/p>\n<p>If we don&#8217;t know whether you are using a function or an object builder, we consider the usage to be a function call if the <em>new<\/em> keyword is not present, and we take it to be an object builder if the <em>new <\/em>keyword is present.<\/p>\n<h3>Behind the scenes<\/h3>\n<p>Want to know what will be sent to the MongoDB database? There are two options in the context menu: C<em>opy JS script to clipboard<\/em> and <em>Show JS Script. <\/em>In the <em>JS Script Preview<\/em> window you can edit the query and run it.<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" width=\"1330\" height=\"1650\" class=\"alignnone size-large wp-image-87229\" src=\"https:\/\/blog.jetbrains.com\/wp-content\/uploads\/2020\/10\/MongDBSQLShowJS.png\"><\/p>\n<p>That&#8217;s it! We are looking forward to receiving your feedback, and we hope our 2020.3 release will help you work with MongoDB!<\/p>\n<h3><\/h3>\n","protected":false},"author":395,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","categories":[826,808,89],"tags":[6245,600,2655],"cross-post-tag":[],"acf":[],"_links":{"self":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/datagrip\/87154"}],"collection":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/datagrip"}],"about":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/types\/datagrip"}],"author":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/users\/395"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/comments?post=87154"}],"version-history":[{"count":5,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/datagrip\/87154\/revisions"}],"predecessor-version":[{"id":191561,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/datagrip\/87154\/revisions\/191561"}],"wp:attachment":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/media?parent=87154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/categories?post=87154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/tags?post=87154"},{"taxonomy":"cross-post-tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/cross-post-tag?post=87154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}