So I was watching a movie viz. Les Miserable, where some convicts sing a song with "Look down" phrase and they sing "Sweet Jesus does not care" for them and they wanted Jesus to look after them . So I thought if I try to say "look down upon us, O sweet Jesus", will it be meaningful? I mean can we use "down" and "upon" two opposite prepositions side-by-side?
Yes, indeed, you may say look down upon (or on); divinities, kings, employers and such superior beings are often spoken of as looking down upon their worshippers, subjects and employees—sometimes benevolently, sometimes wrathfully—and someone on a high building or mountain may look down upon the street or valley below.
Down and upon are not opposite but supplementary here. Down, which is employed as an adverb, not a preposition, designates the direction of looking: the subject is above what is being looked at. Upon is a preposition and heads a prepositional phrase designating what or who is looked at.
There is also a figurative use of the same idea to mean regard with scorn; the subject treats what is looked at as if it were “lower”—inferior:
Students from affluent families look down on those whose families cannot provide them fashionable clothing.
Many 18th- and early 19th-century grammarians looked down upon the recently invented passive progressive construction.
On is more often used with the figurative use and upon with the literal use; but this is by no means a rule.
The collocation VERB + ADVERB of direction + Prepositional phrase is perfectly ordinary: look up at/to, poke around in, put up with, get away with. In many cases a collocation has an idiomatic sense which cannot be derived by analysis of its components, and such collocations are often classed as phrasal verbs; but this is not the case with look down upon.