CROSS APPLY and get inner json node details

 SELECT s.StudentId,

s.StudentName,

s.[Message],

[m].MessageType,

[m].[Subject],

[m].[MessageBody]

FROM Student s 

CROSS APPLY OPENJSON([Message], '$.Messages') sm

CROSS APPLY OPENJSON(sm.value)

WITH

(

MessageType NVARCHAR(50),

[Subject] NVARCHAR(50),

[MessageBody] NVARCHAR(50)

) AS [m]

--Insert into Student values(1,'TestStudent','{"Messages": [{"MessageType": "Email","Subject": "Payment","MessageBody":"Kindly pay your school bill amount {amount}"},{"MessageType": "Sms","Subject": "Payment","MessageBody":"Kindly pay your school bill amount {amount}"}]}')