SQL Join with ObjectId

Kazzy shared this question 20 months ago
Answered

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.

Replies (2)

photo
1

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"

}

photo
1

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"
`)

Leave a Comment
 
Attach a file