SQL Join with ObjectId
This may be a newbie question - but could not find from the forum.
I would like to join two tables: users & receipts. And want to join via the following fields.
- users._id (ObjectId)
- receipts.data.id (string)
I am trying to join like below:
mb.runSQLQuery(`
SELECT * FROM users
JOIN receipts ON users._id = receipts.data.userId
`).sort({_id:-1})
.limit(100)
And the result was:
{
"message" : "SyntaxError: Line: 1 Column: 63, Expected '!=', '%', '*', '+', ',', '-', '--', '/', '/*', '//', '<', '<=', '<>', '=', '>', '>=', 'AND', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'OR', 'ORDER', 'OUTER', 'RIGHT', 'UNION', 'WHERE', [ \\t\\n\\r], [A-Za-z0-9_:], or end of input but '.' found.",
"stack" : "script:2:4",
"sql" : "SELECT * FROM users JOIN receipts ON users._id = receipts.data.userId",
"___" : " ^ ",
"location" : {
"start" : {
"offset" : 62,
"line" : 1,
"column" : 63
},
"end" : {
"offset" : 63,
"line" : 1,
"column" : 64
}
}
}
Then, what am I supposed to do for the problematic field, receipts.data.userId??
Thanks for your time.
The same question
And double quoting the receipts.data.userId won't cause any errors; however, it does not return any records:
SELECT * FROM users
JOIN receipts ON users._id = "receipts.data.userId"
mongo query like below works:
db.users.aggregate([
{
"$project": {
"_id": {
"$toString": "$_id"
},
}
},
{
"$lookup": {
"from": "receipts",
"localField": "_id",
"foreignField": "data.userId",
"as": "receipts"
}
}
])
The problem is that I cannot use something like "toString" as it complains like:
{
"message" : "SQL JOIN only supports Single Equality Join, e.g. SELECT * FROM orders JOIN customers ON orders.customerID=customers.customerID;",
"stack" : "script:2:4"
}
And double quoting the receipts.data.userId won't cause any errors; however, it does not return any records:
SELECT * FROM users
JOIN receipts ON users._id = "receipts.data.userId"
mongo query like below works:
db.users.aggregate([
{
"$project": {
"_id": {
"$toString": "$_id"
},
}
},
{
"$lookup": {
"from": "receipts",
"localField": "_id",
"foreignField": "data.userId",
"as": "receipts"
}
}
])
The problem is that I cannot use something like "toString" as it complains like:
{
"message" : "SQL JOIN only supports Single Equality Join, e.g. SELECT * FROM orders JOIN customers ON orders.customerID=customers.customerID;",
"stack" : "script:2:4"
}
You can think of the "runSQLQuery" method as a stage of the aggregation pipeline, and then transform a complex operation into multiple simple SQL query steps. You can try the following statement
mb.runSQLQuery(`select toString(_id) as _id, * from users`) .runSQLQuery(` SELECT * FROM users JOIN receipts ON users._id = receipts."data.id" `)You can think of the "runSQLQuery" method as a stage of the aggregation pipeline, and then transform a complex operation into multiple simple SQL query steps. You can try the following statement
mb.runSQLQuery(`select toString(_id) as _id, * from users`) .runSQLQuery(` SELECT * FROM users JOIN receipts ON users._id = receipts."data.id" `)Replies have been locked on this page!