Hello
I am looking for a online tool or script,to print MQL/JSON closer to the way people write MQL.
Online i found only either 1 line or new line for every { or ] and it became un-readable if big query,i also tried the pretty of mongoshell.
Thank you
Have a look at jq. It has some nice features. Not sure it will fill your needs but it is a good place to start.
Hi @Takis,
Can you share an example of the sort of formatting you are looking for (sample document before & after)? Folks have different preferences for formatting of MQL and JSON queries.
I second @steevej’s suggestion of jq for pretty-printing JSON (and it is great for quick filtering and wrangling, too). For quick formatting I use pretty-printing in the mongo shell, or work in a more visual tool like MongoDB Compass.
I build more complicated aggregation queries using variables for readability and more straightforward debugging. @steevej shared a great example of this in a recent discussion:
Regards,
Stennie
Hi @Takis,
Can you also confirm whether you were using the classic mongo shell or the new mongosh and the version (via version()).
The new MongoDB Shell generally has better formatting including colour syntax highlighting.
Regards,
Stennie
I tried it on another query and i cant see the contents of an arrays/objects.
Formating is very nice its what i wanted,but i cant see all the query.
How to print the complete query?
I got this from JSON.parse(‘myJSON’) on mongosh
Hi @Takis,
Instead of using JSON.parse(), try using the implicit evaluation in mongosh or console.log(...). This should pretty-print including awareness of MongoDB extended JSON data types.
I created a quick test doc with arrays and objects based on the Atlas Sample Restaurants Dataset and all of the values appeared as I expected:
doc = {
"address": {
"building": "8825",
"coord": [-73.8803827, 40.7643124],
"street": "Astoria Boulevard",
"zipcode": "11369"
},
"borough": "Queens",
"cuisine": "American",
"grades": [ {
"date": new Date("2014-11-15T00:00:00.000Z"),
"grade": "Z",
"score": 38
},
{
"date": new Date("2012-02-10T00:00:00.000Z"),
"grade": "A",
"score": 13
}],
"name": "Brunos On The Boulevard",
"restaurant_id": "40356151"
}
If you are still seeing Object and Array, please provide:
version() in your MongoDB ShellThanks,
Stennie
Hello
Thank you for the reply
I am using mongosh version 0.6.1
I run it from ubuntu terminal
Query(random aggregate command,that i wanted to send to forum yesterday) =
{"aggregate":"testcollA","pipeline":[{"$lookup":{"from":"testcollA","let":{"acid":"$accountId","d":"$postedDate"},"pipeline":[{"$match":{"$expr":{"$eq":["$accountId","$$acid"]}}},{"$group":{"_id":"$accountId","userOldestPostedDate":{"$min":"$postedDate"}}},{"$addFields":{"accountId":"$_id"}},{"$project":{"_id":0}},{"$project":{"userOldestPostedDate":1}}],"as":"joined"}},{"$unwind":{"path":"$joined"}},{"$replaceRoot":{"newRoot":{"$mergeObjects":["$joined","$$ROOT"]}}},{"$unset":["joined"]},{"$group":{"_id":"$accountId","sum":{"$sum":{"$cond":[{"$lte":[{"$subtract":["$postedDate","$userOldestPostedDate"]},2592000000]},1,0]}}}},{"$addFields":{"accountId":"$_id"}},{"$project":{"_id":0}},{"$addFields":{"target":{"$gte":["$sum",5]}}},{"$project":{"_id":0,"accountId":1,"target":1}},{"$merge":{"into":{"db":"testdb","coll":"testcollB"},"on":["accountId"],"whenMatched":"merge","whenNotMatched":"discard"}}],"cursor":{},"maxTimeMS":1200000}
JSON.parse(‘Query_above’) and console.log(Query_above)
Prints the same,query is very well formated but i dont see some embeded arrays/objects
Here only objects in previous query i had arrays and objects.
Also the below is Javascript Object,it would be nice to have valid JSON with keys as strings.
So people can copy paste it.
Solving this is important for the forum also,so far i am sending hard to read MQL code,because
i dont know a way to produce compact MQL code.
Hi @Takis,
Thanks for sharing the sample doc, which helped reproduce the issue. I looked a bit further into the shell implementation and tracked this quirk down to max recursion depth which is currently set to 6 by default: format-output.ts#L162.
It looks like this default was chosen to provide a balance between performance and visible data.
However, you can call util.inspect() directly and provide a depth of “null” (unlimited) to recurse up to the maximum stack size:
util.inspect(json,
{
colors: true,
depth: null
}
)
If you think it might be useful to have a configurable (or larger) default, please raise a suggestion on the MongoDB Feedback Engine and comment here with the link so others can watch and upvote.
Regards,
Stennie
Still its not so good the print : (
But its better than online tools i found so far
Online tool gave 250 lines,mongosh gave 170 lines,Clojure pprint 63 lines.
The query using Clojure pprint as Clojure map is still much better
(but it doesnt have : and , so its not valid json)
Clojure pprint
{"update" "testcoll",
"updates"
[{"q" {"UserId" 1},
"u"
[{"$addFields"
{"Books"
{"$let"
{"vars"
{"newBook" {"Name" "xxx2", "Auth" "xxx2", "Category" 2}},
"in"
{"$cond"
[{"$not" [{"$ne" [{"$type" "$Books"} "missing"]}]}
[{"Category" "$$newBook.Category",
"BookInfos"
[{"Name" "$$newBook.Name", "Auth" "$$newBook.Auth"}]}]
{"$arrayElemAt"
[{"$reduce"
{"input" "$Books",
"initialValue" [[] 0 false],
"in"
{"$let"
{"vars" {"booksIndexAdded" "$$value", "book" "$$this"},
"in"
{"$let"
{"vars"
{"books" {"$arrayElemAt" ["$$booksIndexAdded" 0]},
"index" {"$arrayElemAt" ["$$booksIndexAdded" 1]},
"added" {"$arrayElemAt" ["$$booksIndexAdded" 2]}},
"in"
{"$cond"
[{"$eq" ["$$book.Category" "$$newBook.Category"]}
[{"$concatArrays"
["$$books"
[{"$mergeObjects"
["$$book"
{"BookInfos"
{"$concatArrays"
["$$book.BookInfos"
[{"Name" "$$newBook.Name",
"Auth" "$$newBook.Auth"}]]}}]}]]}
{"$add" ["$$index" 1]}
true]
{"$cond"
[{"$and"
[{"$eq"
["$$index"
{"$subtract" [{"$size" "$Books"} 1]}]}
{"$not" ["$$added"]}]}
[{"$concatArrays"
["$$books"
["$$book"
{"Category" "$$newBook.Category",
"BookInfos"
[{"Name" "$$newBook.Name",
"Auth" "$$newBook.Auth"}]}]]}
{"$add" ["$$index" 1]}
true]
[{"$concatArrays" ["$$books" ["$$book"]]}
{"$add" ["$$index" 1]}
"$$added"]]}]}}}}}}}
0]}]}}}}}],
"upsert" true,
"multi" true}]}
Mongosh same query,with : and commas, but some keys are not strings,so still not valid json
{
update: 'testcoll',
updates: [
{
q: { UserId: 1 },
u: [
{
'$addFields': {
Books: {
'$let': {
vars: {
newBook: { Name: 'xxx2', Auth: 'xxx2', Category: 2 }
},
in: {
'$cond': [
{
'$not': [
{ '$ne': [ { '$type': '$Books' }, 'missing' ] }
]
},
[
{
Category: '$$newBook.Category',
BookInfos: [
{
Name: '$$newBook.Name',
Auth: '$$newBook.Auth'
}
]
}
],
{
'$arrayElemAt': [
{
'$reduce': {
input: '$Books',
initialValue: [ [], 0, false ],
in: {
'$let': {
vars: {
booksIndexAdded: '$$value',
book: '$$this'
},
in: {
'$let': {
vars: {
books: {
'$arrayElemAt': [ '$$booksIndexAdded', 0 ]
},
index: {
'$arrayElemAt': [ '$$booksIndexAdded', 1 ]
},
added: {
'$arrayElemAt': [ '$$booksIndexAdded', 2 ]
}
},
in: {
'$cond': [
{
'$eq': [
'$$book.Category',
'$$newBook.Category'
]
},
[
{
'$concatArrays': [
'$$books',
[
{
'$mergeObjects': [
'$$book',
{
BookInfos: {
'$concatArrays': [
'$$book.BookInfos',
[
{
Name: '$$newBook.Name',
Auth: '$$newBook.Auth'
}
]
]
}
}
]
}
]
]
},
{ '$add': [ '$$index', 1 ] },
true
],
{
'$cond': [
{
'$and': [
{
'$eq': [
'$$index',
{
'$subtract': [
{
'$size': '$Books'
},
1
]
}
]
},
{
'$not': [ '$$added' ]
}
]
},
[
{
'$concatArrays': [
'$$books',
[
'$$book',
{
Category: '$$newBook.Category',
BookInfos: [
{
Name: '$$newBook.Name',
Auth: '$$newBook.Auth'
}
]
}
]
]
},
{
'$add': [ '$$index', 1 ]
},
true
],
[
{
'$concatArrays': [
'$$books',
[ '$$book' ]
]
},
{
'$add': [ '$$index', 1 ]
},
'$$added'
]
]
}
]
}
}
}
}
}
}
},
0
]
}
]
}
}
}
}
}
],
upsert: true,
multi: true
}
]
}
I would like something like Clojure pprint with valid JSON
if someone finds a tool to do this it would be nice to use it,to send compact JSON.
It could help the forum also