JSON_VALUE function is not working as expected in Cloud Spanner query, it is returning incorrect values from nested data blocks, how do I fix it?

Question

JSON_VALUE function is not working as expected in my Query in Google Cloud Spanner. Interestingly, it is selecting the wrong value instead of returning a blank value, but only in certain scenarios.

In my data I have nested blocks, when inner blocks are missing it sometimes incorrectly returns values in other inner blocks of the same name but sit in a different outer block.

In the example below I want to extract the students’ grades, when the Student 1 wasn’t present for term 2 or 3 it should return blank for their grades as these inner blocks don’t exist, instead for term 2 it returns Student 2’s grade (incorrect) but for term 3 it (correctly) returns blank.

Why is it giving the wrong value for Student 1 term 2 but correct for Student 1 term 3?

My data in Cloud Spanner:

insert into json_value_test(Id,full_data_Json )
values (20, ’
{“Student”:
[
    {   “Student_Count”:1, 
        “Term”:
             [  {“English”: “Student_1_English_term_1_grade”, 
                “Maths”: “Student_1_Maths_term_1_grade”}
            ]
    },
    {   “Student_Count”:2,
        ”Term”:
            [   {"English": "Student_2_English_term_1_grade”, 
                "Maths":"Student_2_Maths_term_1_grade"},
                {"English": "Student_2_English_term_2_grade”, 
                "Maths":"Student_2_Maths_term_2_grade"},
                {"English":"Student_2_English_term_3_grade”, 
                "Maths":"Student_2_Maths_term_3_grade”}
            ]
    }
]
}
’)

My query:

select 

JSON_VALUE(full_data_Json, '$.Student[0].Term[0].English') as Student_1_English_term_1_grade
,JSON_VALUE(full_data_Json, '$.Student[0].Term[1].English') as Student_1_English_term_2_grade
,JSON_VALUE(full_data_Json, '$.Student[0].Term[2].English') as Student_1_English_term_3_grade

,JSON_VALUE(full_data_Json, '$.Student[1].Term[0].English') as Student_2_English_term_1_grade
,JSON_VALUE(full_data_Json, '$.Student[1].Term[1].English') as Student_2_English_term_2_grade
,JSON_VALUE(full_data_Json, '$.Student[1].Term[2].English') as Student_2_English_term_3_grade

from json_value_test where Id= 20

The output: image shows Student_1_English_term_2_grade incorrectly retuning the value for Student_2_English_term_2_grade

JSON_VALUE(full_data_Json, '$.Student[0].Term[1].English') as Student_1_English_term_2_grade

is incorrectly returning Student_2_English_term_2_grade instead of blank.

How do I fix this?