can we pass parameter to the JSON_VALUE in Oracle

Question

I have a query which fetches json format data from a column. i want to fetch the data of json by passing a field_name dynamically from the column.

for example

SELECT SUBJECT_MARKS
FROM STUDENT
WHERE STUDENT_ID = 101

result is:

{
    "English": "70",
    "Hindi": "80",
    "Maths": "90",
    "Science": "90",
    "Social": "85"
}

If I want to get the particular subject marks, then the query is:

SELECT JSON_VALUE(SUBJECT_MARKS,'$.Maths')
FROM STUDENT
WHERE STUDENT_ID = 101

Now the result is:

90

Now my requirement is to get the SUBJECT MARKS dynamically while giving the subject name as parameter in the query

SELECT JSON_VALUE(SUBJECT_MARKS,:pSubjectMarks)
FROM STUDENT
WHERE STUDENT_ID = 101

while execute the query when we give the :pSubjectMarks as '$.Science' then it is throwing the error message

ORA-40454: path expression not a literal 40454. 00000 - "path expression not a literal" *Cause: The provided path expression was not a literal (a constant). *Action: Provide a constant path expression. Error at Line: 29 Column: 45

Can anyone help me to find the solution for the query Thanks in advance

Answer 1

You could build the subject you want to get info from into an EXECUTE IMMEDIATE statement. Since all of your subjects are simple strings, you can use the DBMS_ASSERT package to validate the input of the p_subject_name parameter to prevent any SQL injection from happening.

Below is an example on how to build the procedure.

Setup

CREATE TABLE students
AS
    SELECT 101                                                                                        AS student_id,
              EMPTY_CLOB ()
           || '{ "English": "70", "Hindi": "80", "Maths": "90", "Science": "90", "Social": "85" }'    AS subject_marks
      FROM DUAL;

DECLARE
    PROCEDURE print_subject_score (p_student_id students.student_id%TYPE, p_subject_name VARCHAR2)
    IS
        l_sql     VARCHAR2 (1000);
        l_score   VARCHAR2 (5);
    BEGIN
        l_sql :=
               'select json_value(subject_marks, ''$.'
            || DBMS_ASSERT.SIMPLE_SQL_NAME (p_subject_name)
            || ''') from students where student_id = :p_student_id';

        EXECUTE IMMEDIATE l_sql
            INTO l_score
            USING p_student_id;

        DBMS_OUTPUT.put_line (l_score);
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            --Student ID does not exist
            NULL;
    END;
BEGIN
    print_subject_score (p_student_id => 101, p_subject_name => 'English');
    print_subject_score (p_student_id => 101, p_subject_name => 'Test');
    print_subject_score (p_student_id => 102, p_subject_name => 'Maths');
END;
/