When I use an API, as a client I have to handle "success" cases different from "error" cases; I have no choice there. Therefore, you should return an error in situations that the client wants to treat differently, and success in situations that the client wants to treat the same.
If I do a query that could in theory return any number of results, zero, one, two hundred and so on, then you should return "success" whenever the API delivers the complete list of all results. And possibly in cases where there are many result, you returned a partial list of results to avoid an excessive size, and there is an agreed way how I would get the other results. That's because as a client, I often want to handle the case of zero results like the case of more results. I might treat it differently, but I don't want to be forced to.
It is different in the case that I lookup a value. I expect exactly one result, the value that I am looking for. And I need that one result to continue what I want to do in a meaningful way. Here it is much more acceptable to return a status 404 for the case that no value is there, because I do need to handle that case differently anyway.
Summary: If the client expects any number of results, from zero to large numbers, then return "success" if all results are delivered, even if the number is zero. If the client expects exactly one result, then return success if the result is found, and an error if the result is not found.