Hiding whole objects when generating JSON form ORACLE tables-CodePudding

I would like to know is there any "elegant" way to hide whole generated object when one of columns is null?

For example if "tab_2.col_c" is null (because join doesn't return any values for "tab_2"), whole "tab_2" object is hidden, but when "tab_2.col_c" contains data then object "tab_2" is displayed.

tab_1:
|id|col_a|col_b|
|--|-----|-----|
|1 |  a1 | b1  |
|2 |  a2 | b2  |

tab_2:
|id|fk_id|col_c|col_d|
|--|-----|-----|-----|
|11|  2  |  c1 |  d1 |
|22|  2  |  c2 |  d2 |

Query:

select 
 json_object(
         'tab_1_a' VALUE tab_1.col_a,
         'tab_2_b' VALUE tab_1.col_b,
         'tab_2' value json_object('c' value tab2.col_c, 
                                   'd' value tab2.col_d
                                  )                                                                                   
           )
from tab_1, tab2
where tab_1.id = tab_2.fk_id( )
group by tab_1.col_a, tab_2.col_b

Output for "tab_1.id" = 1

{
  "tab_1_a": "a1",
  "tab_1_b": "b1",
  "tab_2": {
    "c": null,
    "d": null
  }
}

Desired output:

{
  "tab_1_a": "a1",
  "tab_1_b": "b1"
}

CodePudding user response：

If you do not want the keys to be shown, you can use the ABSENT ON NULL clause of JSON_OBJECT_T. This will cause {} to still be returned though even if all of the keys do not contain a value.

WITH
    tab_1 (id, col_a, col_b)
    AS
        (SELECT 1, 'a1', 'b1' FROM DUAL
         UNION ALL
         SELECT 2, 'a2', 'b2' FROM DUAL),
    tab_2 (id,
           fk_id,
           col_c,
           col_d)
    AS
        (SELECT 11, 2, 'c1', 'd1' FROM DUAL
         UNION ALL
         SELECT 22, 2, 'c2', 'd2' FROM DUAL)
SELECT tab_1.id,
       json_object (
           'tab_1_a' VALUE tab_1.col_a,
           'tab_2_b' VALUE tab_1.col_b,
           'tab_2' VALUE
               json_object ('c' VALUE tab_2.col_c,
                            'd' VALUE tab_2.col_d
                            ABSENT ON NULL))    AS formatted_json
  FROM tab_1 LEFT JOIN tab_2 ON tab_1.id = tab_2.fk_id
 WHERE tab_1.id = 1;



   ID FORMATTED_JSON
_____ _____________________________________________
    1 {"tab_1_a":"a1","tab_2_b":"b1","tab_2":{}}

If you do not want {} returned, you can use a subquery. If no results are found then it will return null, but you can hide that as well using the ABSENT ON NULL clause again.

WITH
    tab_1 (id, col_a, col_b)
    AS
        (SELECT 1, 'a1', 'b1' FROM DUAL
         UNION ALL
         SELECT 2, 'a2', 'b2' FROM DUAL),
    tab_2 (id,
           fk_id,
           col_c,
           col_d)
    AS
        (SELECT 11, 2, 'c1', 'd1' FROM DUAL
         UNION ALL
         SELECT 22, 2, 'c2', 'd2' FROM DUAL)
SELECT tab_1.id,
       json_object (
           'tab_1_a' VALUE tab_1.col_a,
           'tab_2_b' VALUE tab_1.col_b,
           'tab_2' VALUE
               (SELECT json_object ('c' VALUE tab_2.col_c,
                                    'd' VALUE tab_2.col_d
                                    ABSENT ON NULL)
                  FROM tab_2
                 WHERE tab_2.fk_id = tab_1.id)
           ABSENT ON NULL)    AS formatted_json
  FROM tab_1
 WHERE tab_1.id = 1;


   ID FORMATTED_JSON
_____ __________________________________
    1 {"tab_1_a":"a1","tab_2_b":"b1"}