1 person found this helpful
Unfortunately there isn't a simple way to do this. These operators are per the SQL standard. If you use JSON_OBJECT or JSON_ARRAY, you are certain to get an object or array as a result, even if it's empty as a result of not providing any inputs or specifying the ABSENT ON NULL syntax. To get a SQL NULL as a result would require an extension.
Your choices are to either post-process the overall result using JSON PL/SQL DOM operations, or attempt to prune unwanted empty containers with nested select statements involving JSON_EXISTS. The latter is tricky. Here's an example using the EMP table and a purposely-null value:
select JSON_OBJECT('outer_field' value
from (select JSON_OBJECT('inner_null_field' value '' absent on null) X
from EMP) T
where (JSON_EXISTS(T.X,'$?(@.size() > 0)')))
null on null)
The outer JSON_OBJECT is a simple "foo" : <value> construct, with null on null to ensure that the value shows up in the result so we can see it.
The inner JSON_OBJECT is similar to your case where there is an empty object being returned. In this case just to show the problem I have selected '' (null) as the only value, and also specified absent on null. This ensures that I get a series of empty objects from the inner query from EMP.
This is then surrounded with a select statement that filters away any results where the size() of the result is not greater that 0. The size() item function returns a count of the elements of an array or the fields of an object, so this can be used to filter away results that do not have at least one value. The result in this example is an empty or "no rows" result set, which is interpreted as a SQL null by the outer JSON_OBJECT.
You can request an enhancement to JSON_OBJECT/JSON_ARRAY and we might be able to add some non-standard syntax to the operators to give the behavior you want. We've already done the inverse of this for JSON_OBJECTAGG/JSON_ARRAYAGG, for example. Those operators, per the standard, return SQL null results if there are no rows of input, which some users found inconvenient. Most users prefer to get an empty container (in particular, an empty array for JSON_ARRAYAGG) when there are no rows, instead of a SQL null. We've added non-standard syntax in R19 of the RDBMS to allow for this behavior.
Thanks! Then I know I'm not just missing some simple solution.
I will probably go for the solution of convincing the recipient that the actual behaviour is also correct json.
Thanks and Best Regards
1 person found this helpful
One other suggestion: if you don't have too many input parameters to the inner JSON_OBJECT, you can always use a CASE statement to select a null instead of a JSON_OBJECT. E.g.
CASE WHEN (b.PRODUCT_GROUP_ID IS NULL AND b.PRODUCT_GROUP_NAME IS NULL)
ELSE json_object ('number' VALUE b.PRODUCT_GROUP_ID,
'name' VALUE b.PRODUCT_GROUP_NAME ABSENT ON NULL)