Forum Discussion

sshoaib's avatar
sshoaib
Copper Contributor
Mar 04, 2021

Parsing JSON files

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": "....."
},
},

Windows PowerShell
  • AndySvints's avatar
    AndySvints
    Steel Contributor
    Apr 28, 2021

    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.

Resources