Use case:
Application requires a subset of attributes, based on business rules
Example:
Some students do not require to enter in home address
Database : Oracle
Proposed implementation:
Build json object containing all possible attribute named pairs, then selectively remove specific named pairs
Issue:
Hoped to use native oracle function to remove the specified named pair.
e.g json_object.remove_attribute('home_address');
However Oracle do not appear to provide any such method.
Workaround : Convert json_object to VARCHAR2 string, and then use combination of INSTR and REPLACE to remove named pair.
Illustrative code:
DECLARE
CURSOR cur_student_json (p_s_ref IN VARCHAR2) IS
SELECT JSON_OBJECT(
,'s_surname' value s.s_surname
,'s_forename_1' value s.s_forename_1
,'s_home_address_1' value s.s_home_address_1
RETURNING VARCHAR2 ) student_json
FROM students s
WHERE s.s_ref = p_s_ref;
BEGIN
FOR x IN cur_student_json (p_s_ref) LOOP
vs_student_json:=x.student_json;
EXIT;
END LOOP;
-- Determine student type
vs_student_type:=get_student_type(p_s_ref);
-- Collect list of elements not required, based on student type
FOR x IN cur_json_inorout(vs_student_type) LOOP
-- Remove element from the json
vs_student_json:=json_remove(vs_student_json,x.attribute);
END LOOP;
END;
/
Question:
There must be an elegant method to achieve requirement