Message board

Sometimes you get a log file from Aurora you would like to parse with pgbadger but it is in json format.  Some people like me struggle with these simple conversions so if there is a better way of doing it, please let me know and post a reply here.

This example is based on an Aurora json log file like so ..

{

   "LogFileDate" :  " ........

}

You will need jq installed.

This is what you run

cat sourcefile | jq '.LogFileData' | sed 's/\\"//g' | sed 's/^\"//g' | sed  's/\\n"//g' | sed 's/\\n/\n/g' 

What happens ..

Feed the source file into jq with the filter '.LogFileData'

Get rid of all the \"

Get rid of the the \n" at the end of the string ( In my case, the end of the file contained a \n"

Turn all the literral \n in the file into real \n

This is what you have ...

{
   "LogFileData": "2025-03-09 22:41:09 UTC:10.163.174.195(35716):demo@myapp_stage:[27385]:LOG:  duration: 5346.155 ms  execute a23: SELECT \"prod_type\".* FROM \"prod_type\" WHERE \"prod_type\".\"user_project_id\" = $1 ORDER BY \"prod
_type\".\"created_at\" DESC LIMIT $2\n2025-03-09 22:41:09 UTC:10.163.174.195(35716):demo@myapp_stage:[27385]:DETAIL:  parameters: $1 = 'c2931a31-89bf-4eaa-a0bf-9765d9123d70', $2 = '4'\n"
}

This is what you get ...

2025-03-09 22:41:09 UTC:10.163.174.195(35716):demo@myapp_stage:[27385]:LOG:  duration: 5346.155 ms  execute a23: SELECT prod_type.* FROM prod_type WHERE prod_type.user_project_id = $1 ORDER BY prod_type.created_at DESC LIMIT $2
2025-03-09 22:41:09 UTC:10.163.174.195(35716):demo@myapp_stage:[27385]:DETAIL:  parameters: $1 = 'c2931a31-89bf-4eaa-a0bf-9765d9123d70', $2 = '4'