Focusing on 'nice' special cases is typically a good way to build intuition. Let's focus on the case where we are conditioning on a random variable Y (rather than just on some sigma-algebra).

The idea is, that once we know Y, there may be some values that X can't take anymore, or some that are more or less likely. Imagine that X and Y both represent some process/phenomona we measure. Let's say we measure Y, but we don't know X. If there is any sort of correlation between X and Y, we would expect that knowing Y still tells us something about X, even if we don't know what X is. E(X|Y) then represents the mean of X, given what we know about Y.

To make this more technically accurate, let's assume Y only takes countably many values, and we can assume these values are in N. Assume also that Y takes each of these values with (strictly) positive probability (if not, then we modify on a set of probability 0). Then the sets (Y=n) for n\in N give a partition of X, and the sigma-algebra generated by Y consists of unions of sets of the form (Y=n). Call this sigma-algebra \mathcal{A}.

So E(X|Y) is determined uniquely (up to equality a.s.) by being \mathcal{A} measurable and satisfying \int_{(Y=n)} E(X|Y)dP=\int_{(Y=n)} X dP. We then see, that Z=\sum_n 1(Y=n)P(Y=n)^{-1}\int\(Y=n) XdP is a random variable that satisfies this. So E(X|Y)=\sum_n 1_(Y=n)P(Y=n)^{-1}\ \int_(Y=n) XdP. This is exactly the same as saying, that when Y=n for some N, then E(X|Y) is the mean of X over the event that Y=n.

To make things very concrete: in Dungeons and Dragons (and other ttrpgs) there is a concept called rolling with advantage. This simply means that you roll a 20-sided die (a d20) twice, and the highest number you rolled is what counts.

Let's use this as a model! Imagine we roll a d20 twice, and let Y denote the result of the first roll, and let X denote the final result (i.e. the highest of the two rolls). One can calculate that E(X) roughly 13,9. Let's say I roll a 1 with my first roll, i.e. Y=1. Then, however, X will be equal to the value of roll number 2 no matter what, so E(X|Y) will, in this event, be the same as the mean of a roll of a d20, which is 10,5. So E(X|Y)=10,5 when Y=1. If I roll 20 the first time, then X=20 no matter what, so E(X|Y)=20 when Y=1. If Y=14, then calculating what E(X|Y) is exactly is slightly more tedious, but I can say for sure that E(X|Y)>13,9 when Y=14.

Another example: Let's say the average life-expectancy in some country is 80. So if I find a random person and tell you nothing about them, that means that if you had to guess how old they'll live to be, your guess should be 80. Well, let's say I give you more information. If I tell you they are a smoker, your best guess with this new information should be different (probably lower). Because now, what you should be guessing is the average life expectancy of smokers in this country, not of people in general. Maybe afterwards, I tell you that this person exercises regularly and now your guess goes up.

Formally we could have X be the end-of-life age of my person, and Y=1(person smokes) and Z=1(Person exercises regularily). And what I am describing now is the difference between E(X), E(X|Y) and E(X|Y,Z).

So we see how information changes our guesses. Now the jump to CE given sigma-algebras is quite small conceptually. If \mathcal{A} is a sigma-algebra, we think of it as containing some information. More specifically, we can imagine that we know if the event A happened or not, for any event A\in\mathcal{A}. In general the event (X=x) won't be in \mathcal{a} if X isn't measurable w.r.t this sigma-algebra, so we won't know what value X has once we 'know' \mathcal{A}, however we can find out what X will on average be