By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH t AS
(
SELECT '{
"Info": {
"code": "SPPACK"
},
"user": {
"firstName": "John",
"lastName": "Smith",
"login": {
"loginType": "MOBILE_NUMBER",
"userName": "91817343123"
}
}
}' jstr
FROM dual
)
SELECT LoginType, userName
FROM t,
JSON_TABLE(jstr, '$'
COLUMNS (NESTED PATH '$."user"[*]."login"[*]'
COLUMNS (
LoginType VARCHAR2 PATH '$."loginType"',
userName VARCHAR2 PATH '$."userName"')
))
| LOGINTYPE | USERNAME |
|---|---|
| MOBILE_NUMBER | 91817343123 |