How to parse json data returned fro SQL query in Analytics report.

[IDAM09]

We are trying to create an audit log report and export the results in Excel.

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', JSON_VALUE(ua.DETAIL,'$.message') 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

Query Output looks like below. I want to fetch only the message value from the last column.

Object Type	Action Taken	Accessed By	IP Address	Event Time	Message
CONNECTIONS	Show	xyz	100.0.00.200	21/08/2023 4:55	{"data":"","objectName":"Connection List","message":"User xyz requested for list of Connections"}

 Tried the below query :

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', JSON_VALUE(ua.DETAIL,'$.message') 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

Error: Operation not allowed as you are entering a value that resembles or contains SQL query

Any idea or suggestion to parse the json data?

[rushikeshvartak · Accepted solution]

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