Parsing JSON files

[sshoaib]

Hi All I am trying to add new key value layer to existing JSON files (SQL logs that will be sent to cloud).

So the current JSON output is like:

[
{
"Column1": ".....",
"session_server_principal_name": "",
"server_principal_name": "....",
"database_principal_name": "",
"target_database_principal_name": "",
"target_server_principal_name": "",
"server_instance_name": "S.....",
"statement": "....."
},

I would like to add a parent key values like this:

{
"split_events": true,
"events" [
{
"Column1": ".....",
"session_server_principal_name": "",
"server_principal_name": "....",
"database_principal_name": "",
"target_database_principal_name": "",
"target_server_principal_name": "",
server_instance_name": "S.....",
"statement": "....."
},
},

[#2]

Hello @sshoaib,

If I understood you correctly you need to enrich your existing Json file with additional key value pair and also increase file depth.

One of the approaches could be something like this:

Input Json file containing array of your events:

[
    {
    "Column1": ".....",
    "session_server_principal_name": "",
    "server_principal_name": "....",
    "database_principal_name": "",
    "target_database_principal_name": "",
    "target_server_principal_name": "",
    "server_instance_name": "S.....",
    "statement": "....."
    },
    {
        "Column1": ".....",
        "session_server_principal_name": "",
        "server_principal_name": "....",
        "database_principal_name": "",
        "target_database_principal_name": "",
        "target_server_principal_name": "",
        "server_instance_name": "S.....",
        "statement": "....."
        }
]

 Couple of PowerShell lines to enrich it:

#Assuming your input json  is stored in a file
$JsonFile=Get-Content .\File.json | ConvertFrom-Json

$EnrichedJson=@{
    split_events=$true
    events=$JsonFile
}

#Output result to a file
$Props | ConvertTo-Json | Out-File JsonFileOutput.json

#Or store it in variable
$Result=$Props | ConvertTo-Json

Output file:

{
  "split_events": true,
  "events": [
    {
      "Column1": ".....",
      "session_server_principal_name": "",
      "server_principal_name": "....",
      "database_principal_name": "",
      "target_database_principal_name": "",
      "target_server_principal_name": "",
      "server_instance_name": "S.....",
      "statement": "....."
    },
    {
      "Column1": ".....",
      "session_server_principal_name": "",
      "server_principal_name": "....",
      "database_principal_name": "",
      "target_database_principal_name": "",
      "target_server_principal_name": "",
      "server_instance_name": "S.....",
      "statement": "....."
    }
  ]
}

Hope that helps.