SQL: Extract value of a key from a JSON string in a column

[AS5278]

Hi All,

We are facing an issue. We have created an analytics report for the Audit Trail. We have selected the column 'detail' from the 'userlogin_access' as the 'Message'. 

Now the problem is that this detail column has the data in form of a JSON string

Example: 

{"data":"customproperty10:, customproperty12:, customproperty47Label:, customproperty11:, customproperty9Label:, remoteHost:103.170.182.129, customproperty18:, customproperty17:, customproperty19:, customproperty16Label:, customproperty14:, customproperty13:, customproperty50Label:, customproperty16:, customproperty15:, allowChangePassword_sqlquery:*****, customproperty48Label:, requestowner:, DISABLENEWACCOUNT:on, customproperty33Label:, customproperty17Label:, customproperty29Label:, customproperty15Label:, customproperty34Label:, customproperty18Label:, customproperty45Label:, savmodule:, customproperty8Label:, customproperty31Label:, customproperty20Label:, ownerkeytemp:, requestownertype:0, customproperty4:, customproperty5:, customproperty2:, securitysystemkey.id:22, customproperty3:, customproperty1:, customproperty44Label:, displayName:Oracle Subscription Management Cloud (OSMC), customproperty19Label:, accountNameRule:, customproperty13Label:, customproperty1Label:, customproperty7Label:, customproperty28Label:, customproperty22Label:, customproperty8:, customproperty9:, customproperty6:, customproperty7:, DISABLEREMOVEACCOUNT:on, customproperty51Label:, customproperty38Label:, customproperty32Label:, customproperty35Label:, Copy Service Account Request Data:on, securitysystemkey:id:22, customproperty54Label:, ownerType:, customproperty39Label:, actionUri:\/endpoints\/save, customproperty55Label:, customproperty10Label:, customproperty3Label:, objectType:ENDPOINT, CONNECTIONCONFIG:, accessquery:, customproperty11Label:, USERLOGINS_KEY:com.saviynt.ecm.utility.UserLogins : 461050, customproperty56Label:, customproperty25Label:, customproperty4Label:, customproperty41Label:, remoteAddress:103.170.182.129, customproperty43:, customproperty42:, eventId:1716202097908, customproperty45:, customproperty12Label:, customproperty26Label:, customproperty44:, customproperty41:, customproperty40:, customproperty23Label:, customproperty37Label:, customproperty42Label:, customproperty5Label:, customproperty2Label:, customproperty32:, customproperty30Label:, customproperty31:, customproperty34:, customproperty6Label:, customproperty33:, customproperty36Label:, customproperty30:, description:, customproperty39:, customproperty36:, customproperty53Label:, customproperty35:, customproperty38:, customproperty37:, customproperty21Label:, customproperty14Label:, customproperty52Label:, requestownertemp:, customproperty21:, customproperty20:, customproperty23:, customproperty22:, customproperty29:, customproperty28:, customproperty40Label:, customproperty25:, ownerkey:, customproperty24:, customproperty27:, customproperty26:, actionType:Create, customproperty24Label:, customproperty27Label:, endpointname:OSMC, customproperty43Label:, customproperty46Label:, customproperty49Label:","objectName":"OSMC","message":"Endpoint OSMC created by user DF6789"}

From the above String we just want the value for the key 'message' as the Message column in our custom Audit Trail report. Is this achievable through SQL queries?. Please advice. This is urgent.

Thanks,

Atul Singh

[naveenss]

Hi @AS5278 you can use substring_index to retrieve the message value. 

[rushikeshvartak]

SELECT ua.typeofaccess                                                      AS
       'Object Type',
       ua.actiontype                                                        AS
       'Action Taken',
       u.username                                                           AS
       'Accessed By',
       ua.ipaddress                                                         AS
       'IP Address',
       ua.accesstime                                                        AS
       'Event Time',
       ua.detail                                                            AS
       'Message',
       Substring_index(Substring_index(ua.detail, 'message":"', -1), '"', 1)AS
       'ShortMessage'
FROM   users u,
       userlogin_access ua,
       userlogins ul
WHERE  ul.loginkey = ua.loginkey
       AND ul.userkey = u.userkey
       AND ua.detail IS NOT NULL 

refer https://forums.saviynt.com/t5/identity-governance/how-to-parse-json-data-returned-fro-sql-query-in-a...