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;
/

Answer 2

From Oracle 12 (when support for JSON functions was added), you could whitelist all the possible options in a CASE expression using either:

SELECT CASE :pSubjectMarks
       WHEN 'English' THEN JSON_VALUE(SUBJECT_MARKS,'$.English')
       WHEN 'Hindi'   THEN JSON_VALUE(SUBJECT_MARKS,'$.Hindi')
       WHEN 'Maths'   THEN JSON_VALUE(SUBJECT_MARKS,'$.Maths')
       WHEN 'Science' THEN JSON_VALUE(SUBJECT_MARKS,'$.Science')
       WHEN 'Social'  THEN JSON_VALUE(SUBJECT_MARKS,'$.Social')
       END as subject_marks
FROM   STUDENT s

or:

SELECT CASE :pSubjectMarks
       WHEN 'English' THEN english
       WHEN 'Hindi'   THEN hindi
       WHEN 'Maths'   THEN maths
       WHEN 'Science' THEN science
       WHEN 'Social'  THEN social
       END as subject_marks
FROM   STUDENT s
       CROSS APPLY JSON_TABLE(
         s.subject_marks,
         '$'
         COLUMNS (
           ENGLISH NUMBER PATH '$.English',
           HINDI   NUMBER PATH '$.Hindi',
           MATHS   NUMBER PATH '$.Maths',
           SCIENCE NUMBER PATH '$.Science',
           SOCIAL  NUMBER PATH '$.Social'
         )
       ) j
WHERE  STUDENT_ID = 101;

db<>fiddle here

Answer 3

Since you have an access to the JSON_VALUE function, then your Oracle version is at least 12.1. This version also provides an access to the local PL/SQL declarations in SQL queries, where you have full dynamic access to JSON keys.

So you may use JSON_OBJECT_T data type for this. As you may see, path is passed as a column value (expression, not a literal), and you may use bind variable in place of an expression.

with function get_mark(
  p_marks in varchar2,
  p_subj in varchar2
)
return number
as
  json JSON_OBJECT_T;
begin
  json := JSON_OBJECT_T(p_marks);
  return json.get_String(p_subj);
end;

select
  t.column_value as subj,
  get_mark(SUBJECT_MARKS, t.column_value) as mark
from students
  cross join sys.odcivarchar2list(
    'English',
    'Hindi',
    'Maths', 
    'Science',
    'Social',
    'something'
  )  t
where student_id = 101

SUBJ      | MARK
:-------- | ---:
English   |   70
Hindi     |   80
Maths     |   90
Science   |   90
Social    |   85
something | null

db<>fiddle here