1

I have a JSON string like the following in a VARCHAR2 column in an Oracle database.

{
  "name": "Bob",
  "age": "20",
  "extras": {
    "address": "123 Main St"
  }
}

I want to remove the "extras" node from the JSON string and return the result, like:

{
  "name": "Bob",
  "age": "20"
}

I already know how to parse out pieces of a JSON string using Oracle JSON functions like json_value, json_table, json_query. Unfortunately, I am stuck on Oracle 12.1 so I cannot use functions like json_object, json_aggobject, which were added in 12.2.

I tried using a path syntax like json_query(data, '$["name"]["age"]') (see this) but it appears Oracle doesn't support that kind of path syntax.

I could solve this by explicitly extracting each of the known keys from the original object and recombining using concatenation, like:

select '{"name":"' || json_value(data,'$.name') 
     ||'","age":"' || json_value(data,'$.age') || '"}' result
from (
    select '{"name":"Bob","age":"20","extras":{"address":"123 Main St"}}' data
    from dual);

But I want to do this generically so that I don't have the keys hard-coded into the SQL expression (I expect that other keys might be added in the future).

How can I just remove the "extras" node without hard-coding a list of keys in my SQL statement?

GreenGiant
  • 4,930
  • 1
  • 46
  • 76

1 Answers1

1

In oracle 12.2 the are new calls you can do to remove it. In the steps below I build your string, output it, remove the "extras" and output it again:

SET SERVEROUTPUT ON
DECLARE
  l_obj JSON_OBJECT_T;
BEGIN
  -- New empty object using constructor.
  -- With or without the NEW keyword.
  l_obj := JSON_OBJECT_T();
  DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
  l_obj := NEW JSON_OBJECT_T();
  DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);

 -- New object based on some JSON text using constructor.
 -- With or without the NEW keyword.
  l_obj := JSON_OBJECT_T('{
  "name": "Bob",
  "age": "20",
  "extras": {
  "address": "123 Main St"
  }
}');
  DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);

  -- remove "extras"
  l_obj.remove('extras');
  DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
END;
/
boyzers
  • 121
  • 8