I think it depends on the depth of the relationship. Not everyone deserves to see your soft underbelly.
If you have a connection with someone and have built trust, being vulnerable can feel wonderful. It takes the weight of the world off you for a little while, and it can bring you closer to your partner.
However, if you get all weepy because your take-out order was messed up, that’s a turn off. 😄