I have a table with column contains a request from an API that contains special characters and it lo-CodePudding

{
  "Info": {
    "code": "SPPACK"
  },
  "user": {
    "firstName": "John",
    "lastName": "Smith",
    "login": {
      "loginType": "MOBILE_NUMBER",
      "userName": "91817343123"
    }
  }
}

I want to retrieve only LoginType and Username values from the JSON above such as

LoginType	Username
MOBILE_NUMBER	91817343123

How can I write a query in oracle to retrieve these values? Please help.

CodePudding user response：

You can use JSON_TABLE() function such as

SELECT LoginType, userName
  FROM t,
       JSON_TABLE(jstr, '$'
                        COLUMNS (NESTED PATH '$."user"[*]."login"[*]'
                        COLUMNS (
                                 LoginType VARCHAR2 PATH '$."loginType"', 
                                 userName  VARCHAR2 PATH '$."userName"') 
                   ))

Demo