0

I have a JSON column in an Oracle DB where it was populated without the ABSENT ON NULL option and there are some pretty long lengths because of this.

I would like to trim things down and have created a new table similar to the first but I would like to select the JSON from form the old, add the ABSENT ON NULL option and place the new values in reducing the column length.

So I can see the JSON easy enough like

SELECT json_query(json_data,'$') FROM table;

This will give a result like:

 {
  "REC_TYPE_IND":"1",
  "ID":"1234",
  "OTHER_ID":"4321",
  "LOCATION":null,
  "EFF_BEG_DT":"19970101",
  "EFF_END_DT":"99991231",
  "NAME":"Joe",
  "CITY":null
 }

When I try to remove the null values like

 SELECT json_object (json_query(json_data,'$') ABSENT ON NULL
                                         RETURNING VARCHAR2(4000)
                     ) AS col1 FROM table;

I get the following: ORA-02000: missing VALUE keyword

I assume this is because the funcion json_object is expecting the format:

json_object ('REC_TYPE_IND'    VALUE '1',
             'ID'              VALUE '1234')

Is there a way around this, to turn the JSON back into values that JSON_OBJECT can recognize like above or is there a function I am missing?

programmerNOOB
  • 121
  • 3
  • 19
  • As you said there are some long lengths, are you expecting this to remove *all* keys with null value from an object with lots of key/value pairs? I don't think that's how it works... Maybe if you edit your question to include some sample JSON values and what you want to end up with for those someone might be able to suggest an approach. – Alex Poole Jul 09 '21 at 16:43
  • Updated to include some example output. I am thinking this is more of an exercise of generating a string that represents what the function JSON_OBJECT so it is a matter of getting a output of the existing JSON but in the format of 'COLUMN_NAME VALUE 'VALUE', – programmerNOOB Jul 10 '21 at 00:01

0 Answers0