如何从mongodb中的字段中的对象数组中检索部分对象(how to retrieve partial objects from object array in a field in mongodb)
我有一个猫鼬模式像 -
db.foo.insert({one:'a',friends:[{two:'b',three:'c'},{two:'d',three:'e'},{two:'f',three:'G'}]})
现在我想要的是两个只检索
friends
数组的'two'
部分,即我想在friends
数组的每个对象中找到two
所有值的数组这样的投影可能在mongodb中的输出看起来像 -['b','d','f']
I have a mongoose schema like -
db.foo.insert({one:'a',friends:[{two:'b',three:'c'},{two:'d',three:'e'},{two:'f',three:'G'}]})
now what i want is two retrieve only the
'two'
part offriends
array that is I want to find an array of all the values oftwo
in each object infriends
array Is such a projection possible in mongodb where in the output looks like -['b','d','f']
原文:https://stackoverflow.com/questions/27606591
最满意答案
你似乎需要做的是将pinme表连接到自身,然后再次反对自己找到匹配。 对阻塞表和请求表执行LEFT OUTER JOIN以及结果,以排除已存在阻塞或请求记录的任何记录。
像这样: -
SELECT DISTINCT c.aPerson FROM (SELECT friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend FROM pinme WHERE friendpin = $userlogged) a INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) b ON a.aFriend = b.aFriend INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) c ON b.aPerson = c.aFriend LEFT OUTER JOIN (SELECT user1_id AS blocked FROM blocked WHERE user2_id = $userlogged UNION SELECT user2_id AS aFriend FROM blocked WHERE user1_id = $userlogged) d ON c.aPerson = d.blocked LEFT OUTER JOIN (SELECT senderpin AS blocked FROM request WHERE friendpin = $userlogged UNION SELECT friendpin AS aFriend FROM request WHERE senderpin = $userlogged) e ON c.aPerson = e.blocked WHERE d.blocked IS NULL AND e.blocked IS NULL AND c.aPerson != $userlogged
编辑 - 修改建议
SELECT DISTINCT c.aPerson FROM (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme WHERE friendpin = $userlogged) a -- Find friends INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) b -- Find friends of friends who are not the original person ON a.aFriend = b.aFriend AND a.aPerson != b.aFriend LEFT OUTER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme WHERE friendpin = $userlogged) c -- Find friends ON c.aFriend = b.aPerson LEFT OUTER JOIN (SELECT user1_id AS blocked FROM blocked WHERE user2_id = $userlogged UNION SELECT user2_id AS aFriend FROM blocked WHERE user1_id = $userlogged) d ON b.aPerson = d.blocked LEFT OUTER JOIN (SELECT senderpin AS blocked FROM request WHERE friendpin = $userlogged UNION SELECT friendpin AS aFriend FROM request WHERE senderpin = $userlogged) e ON b.aPerson = e.blocked WHERE d.blocked IS NULL AND e.blocked IS NULL AND c.aFriend IS NULL AND b.aPerson != $userlogged
What you seem to need to do is join the pinme table against itself, and then against itself again to find the matches. Do a LEFT OUTER JOIN with the results of that against the blocked table and against the request table to exclude any records where there is already a blocked or request record.
Something like this:-
SELECT DISTINCT c.aPerson FROM (SELECT friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend FROM pinme WHERE friendpin = $userlogged) a INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) b ON a.aFriend = b.aFriend INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) c ON b.aPerson = c.aFriend LEFT OUTER JOIN (SELECT user1_id AS blocked FROM blocked WHERE user2_id = $userlogged UNION SELECT user2_id AS aFriend FROM blocked WHERE user1_id = $userlogged) d ON c.aPerson = d.blocked LEFT OUTER JOIN (SELECT senderpin AS blocked FROM request WHERE friendpin = $userlogged UNION SELECT friendpin AS aFriend FROM request WHERE senderpin = $userlogged) e ON c.aPerson = e.blocked WHERE d.blocked IS NULL AND e.blocked IS NULL AND c.aPerson != $userlogged
EDIT - Modified suggestion
SELECT DISTINCT c.aPerson FROM (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme WHERE friendpin = $userlogged) a -- Find friends INNER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme) b -- Find friends of friends who are not the original person ON a.aFriend = b.aFriend AND a.aPerson != b.aFriend LEFT OUTER JOIN (SELECT senderpin AS aPerson, friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend, friendpin AS aPerson FROM pinme WHERE friendpin = $userlogged) c -- Find friends ON c.aFriend = b.aPerson LEFT OUTER JOIN (SELECT user1_id AS blocked FROM blocked WHERE user2_id = $userlogged UNION SELECT user2_id AS aFriend FROM blocked WHERE user1_id = $userlogged) d ON b.aPerson = d.blocked LEFT OUTER JOIN (SELECT senderpin AS blocked FROM request WHERE friendpin = $userlogged UNION SELECT friendpin AS aFriend FROM request WHERE senderpin = $userlogged) e ON b.aPerson = e.blocked WHERE d.blocked IS NULL AND e.blocked IS NULL AND c.aFriend IS NULL AND b.aPerson != $userlogged
相关问答
更多-
有什么数据库专业书籍介绍?[2022-05-19]
mysql -
我一般认为LEFT JOIN更容易理解。 SELECT hc.id, u.id, u.name, c.id, c.title FROM courses as c -- must exist LEFT JOIN users as u on 1=1 -- all users, optional LEFT JOIN hidden_courses as hc on hc.user_id=u.id and hc.course_id=c.id -- optional 假设没有用户,那么你想要没有结果吗? 如果是这样, ...
-
这应该是为了这个目的: SELECT o.order_id, order_product.model, o.name, o.value, GROUP_CONCAT(o.order_id, o.name, o.value SEPARATOR ',') AS Options FROM `order_option` AS o LEFT JOIN `order` AS oo on o.order_id = oo.order_id LEFT JOIN order_product ON o.order_i ...
-
看起来你正在得到一个查询的笛卡儿式产品...尝试从你的子查询中获得你的SUM()值... SELECT b.nBoutiqueID, b.sBoutiqueName, b.Status, bs.StatsViewCount, ps.ProductViewCount, ps.ProductLinkClickCount, ps.ProductWishListCount, ps.ProductReferre ...
-
你可以加入表格。 根据您当前的数据定义,无需外部联接或任何内容。 SELECT * FROM Orders o, customers c, orderstatus s WHERE o.customerid = c.customerid AND o.order_status_id = s.order_status_id AND c.firstname = 'OM' AND c.LASTNAME = 'The Eternity' AND o.orderid = 752 AND ... You can jus ...
-
在MySQL中查询2个表(Query in MySQL between 2 tables)[2022-07-20]
我认为查询很好,但我会使用反连接。 SELECT cp.ProductID, ProductTitle FROM cms_products cp LEFT JOIN cms_group_products cgp ON (cp.ProductID = cgp.ProductID AND gID = 1000) WHERE cgp.ProductID IS NULL I think the query is fine, but I would use an anti-join. SELECT cp.Produ ... -
你似乎需要做的是将pinme表连接到自身,然后再次反对自己找到匹配。 对阻塞表和请求表执行LEFT OUTER JOIN以及结果,以排除已存在阻塞或请求记录的任何记录。 像这样: - SELECT DISTINCT c.aPerson FROM (SELECT friendpin AS aFriend FROM pinme WHERE senderpin = $userlogged UNION SELECT senderpin AS aFriend FROM pinme WHERE friendpin = ...
-
尝试使用JOIN和COUNT SELECT COUNT(*) ads_total FROM ad a JOIN adcat c ON a.id = c.adid WHERE c.catid = 1 AND a.userid = 725 SQLFiddle Try use JOIN and COUNT SELECT COUNT(*) ads_total FROM ad a JOIN adcat c ON a.id = c.adid WHERE c.cat ...
-
MySQL查询三个表(MySQL query for three tables)[2024-01-31]
SELECT a.person_ID, MAX(c.value_ID) value_ID, b.attribute_name , MAX(c.value) Value FROM people a CROSS JOIN attributes b LEFT JOIN `values` c ON a.value_ID = c.value_ID AND ... -
请尝试以下查询: - SELECT u.user_name,u.first_name, lead_table.lead_count,lead_table.avgdays FROM users u join users_cstm uc on u.id=uc.id_c left join (SELECT l.assigned_user_id, AVG(l.date_modified) AS avgdays, count(*) AS lead_count FROM leads ...