3 Replies Latest reply on Jun 3, 2019 8:16 PM by dmcmahon-Oracle

    Generating json, remove whole section when null

    Ria_B

      Hi

      I'm generating json from relational data. I have some fields that  doesn't appear on all rows. Is it possible to remove the whole section when the data is null?

       

      'productGroup' VALUE

                  json_object ('number' VALUE b.PRODUCT_GROUP_ID,

                                      'name' VALUE b.PRODUCT_GROUP_NAME ABSENT ON NULL),

      With values:

      "productGroup": {

      "number": 652,

      "name": "Läsk"

      },

       

      When the values are null i get:

      "productGroup": {},

       

      Desired result:

      The the whole section should be omitted.

       

      Thanks and best regards

      Ria_B

        • 1. Re: Generating json, remove whole section when null
          dmcmahon-Oracle

          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

                             (select T.X

                                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)

          from DUAL;

          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.

          1 person found this helpful
          • 2. Re: Generating json, remove whole section when null
            Ria_B

            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

            Ria_B

            • 3. Re: Generating json, remove whole section when null
              dmcmahon-Oracle

              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.

               

              'productGroup' VALUE

                          CASE WHEN (b.PRODUCT_GROUP_ID IS NULL AND b.PRODUCT_GROUP_NAME IS NULL)

                                       THEN NULL

                                     ELSE json_object ('number' VALUE b.PRODUCT_GROUP_ID,

                                                                   'name' VALUE b.PRODUCT_GROUP_NAME ABSENT ON NULL)

                                      END,

              ...

              1 person found this helpful