Separating JSON objects by their key value

Question

Separating JSON objects by their key value

At the moment, I am focused on retrieving the expiry status of Azure AD applications. I have collected approximately 1700 AD applications and added a new key pair (status) to the JSON object based on the expiration date of the secret.

1) valid 2) Expired 3) expiring soon

After extracting all applications into a JSON file, my next task is to divide the single file into three separate files based on the mentioned statuses below:

[
    {
        "DisplayName": "Reporter-dev",
        "ObjectId": null,
        "ApplicationId": {
            "value": "62838283828288282828828288282828",
            "Guid": "62838283828288282828828288282828"
        },
        "KeyId": "62838283828288282828828288282828",
        "Type": "Password",
        "StartDate": {
            "value": "/Date(1590537256000)/",
            "DateTime": "27 May 2020 05:24:16"
        },
        "EndDate": {
            "value": "/Date(1653609256000)/",
            "DateTime": "27 May 2022 05:24:16"
        },
        "Ownername": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="72011a1706060b32151f131b1e5c111d1f">[email protected]</a>",
        "Status": "Valid"
    },
    {
        "DisplayName": "azure-cli-2018",
        "ObjectId": null,
        "ApplicationId": {
            "value": "52388282828828288273673282932739223",
            "Guid": "52388282828828288273673282932739223"
        },
        "KeyId": "52388282828828288273673282932739223",
        "Type": "Password",
        "StartDate": {
            "value": "/Date(1568849784000)/",
            "DateTime": "19 September 2019 05:06:24"
        },
        "EndDate": {
            "value": "/Date(1600472184000)/",
            "DateTime": "19 September 2020 05:06:24"
        },
        "Ownername": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bad0d5c9dfcad2faddd7dbd3d694d9d5d7">[email protected]</a>",
        "Status": "Expired"
    },
    {
        "DisplayName": "azure-cli-2019",
        "ObjectId": null,
        "ApplicationId": {
            "value": "26382882828828282882828282828",
            "Guid": "26382882828828282882828282828"
        },
        "KeyId": "26382882828828282882828282828",
        "Type": "Password",
        "StartDate": {
            "value": "/Date(1576143476000)/",
            "DateTime": "12 December 2019 15:07:56"
        },
        "EndDate": {
            "value": "/Date(1607765876000)/",
            "DateTime": "12 December 2020 15:07:56"
        },
        "Ownername": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="502a2a2a2a2a2a2a2a10373d31393c7e333f3d">[email protected]</a>",
        "Status": "About to Expire"
    }
]

json powershell

Answer 1

Answer №1

To extract and separate JSON data based on their status, the below code snippet will be helpful. You can customize the paths in

$JSONPath, $ValidPath, $ExpiredPath, $ExpiredSoonPath

according to your requirements.

Make sure that the content within $JSONPath is valid JSON for this to function correctly. Although not the most efficient or elegant method, it gets the job done.

$JSONPath = "C:\PS\JT\JSON.txt"

$JSONObj = Get-Content $JSONPath | ConvertFrom-Json

$ValidPath = "C:\PS\JT\Valid.txt"
$ExpiredPath = "C:\PS\JT\Expired.txt"
$ExpireSoonPath = "C:\PS\JT\ExpireSoon.txt"


$JSONObj | Where {$_.Status -eq "Valid"} | ConvertTo-Json | Out-File $ValidPath
$JSONObj | Where {$_.Status -eq "Expired"} | ConvertTo-Json | Out-File $ExpiredPath
$JSONObj | Where {$_.Status -eq "About to Expire"} | ConvertTo-Json | Out-File $ExpireSoonPath

Answer 2

To extract and separate JSON data based on their status, the below code snippet will be helpful. You can customize the paths in

$JSONPath, $ValidPath, $ExpiredPath, $ExpiredSoonPath

according to your requirements.

Make sure that the content within $JSONPath is valid JSON for this to function correctly. Although not the most efficient or elegant method, it gets the job done.

$JSONPath = "C:\PS\JT\JSON.txt"

$JSONObj = Get-Content $JSONPath | ConvertFrom-Json

$ValidPath = "C:\PS\JT\Valid.txt"
$ExpiredPath = "C:\PS\JT\Expired.txt"
$ExpireSoonPath = "C:\PS\JT\ExpireSoon.txt"


$JSONObj | Where {$_.Status -eq "Valid"} | ConvertTo-Json | Out-File $ValidPath
$JSONObj | Where {$_.Status -eq "Expired"} | ConvertTo-Json | Out-File $ExpiredPath
$JSONObj | Where {$_.Status -eq "About to Expire"} | ConvertTo-Json | Out-File $ExpireSoonPath

Answer 3

Answer №2

If you consider a scenario where something as basic as the example below could be effective, but due to the lack of code visibility, there is a certain level of speculation involved...

$collection = $json | ConvertFrom-Json
$valid = @(); $exp = @(); $soon = @(); $unknown = @()

foreach($item in $collection) {
    
    switch ($item.Status)
    {
        'Valid' { $valid += $item }
        'Expired' { $exp += $item }
        'About to Expire' { $soon += $item }
            Default { $unknown += $item}
    }
}

$valid | Out-File .\Valid.txt -Append
$exp | Out-File .\Expired.txt -Append
$soon | Out-File .\AboutToExpire.txt -Append
$unknown | Out-File .\Unknown.txt -Append

Alternatively, if the desired output is in JSON format as shown in the previous example;

$valid | ConvertTo-Json | Out-File .\Valid.txt -Append

and so forth

Answer 4

If you consider a scenario where something as basic as the example below could be effective, but due to the lack of code visibility, there is a certain level of speculation involved...

$collection = $json | ConvertFrom-Json
$valid = @(); $exp = @(); $soon = @(); $unknown = @()

foreach($item in $collection) {
    
    switch ($item.Status)
    {
        'Valid' { $valid += $item }
        'Expired' { $exp += $item }
        'About to Expire' { $soon += $item }
            Default { $unknown += $item}
    }
}

$valid | Out-File .\Valid.txt -Append
$exp | Out-File .\Expired.txt -Append
$soon | Out-File .\AboutToExpire.txt -Append
$unknown | Out-File .\Unknown.txt -Append

Alternatively, if the desired output is in JSON format as shown in the previous example;

$valid | ConvertTo-Json | Out-File .\Valid.txt -Append

and so forth

Answer 5

Answer №3

In addition to the existing responses, here is another approach utilizing pipelines.

Get-Content -Path .\sample.json -Raw | ConvertFrom-Json | 
    ForEach-Object { $_ | ConvertTo-Json | Out-File -FilePath "$($_.Status).json" }

Breakdown

Extract JSON data using Get-Content, ensuring the usage of the -Raw parameter to receive the data as a single string while preserving line breaks instead of an array of strings(default behavior).
Pipe the data into ConvertFrom-Json for deserialization into a
```
System.Management.Automation.PSCustomObject
```
.
Iterate through the objects using Foreach-Object and convert each object into a JSON file using ConvertTo-Json along with Out-File. Note that the default depth for ConvertTo-Json is 2, so adjust the depth using the -Depth parameter if your JSON files have deeper levels.

If it helps with clarity, a traditional foreach loop can also be utilized:

$json = Get-Content -Path .\sample.json -Raw | ConvertFrom-Json

foreach ($object in $json) {
    $object | ConvertTo-Json | Out-File -FilePath "$($object.Status).json"
}

Answer 6

In addition to the existing responses, here is another approach utilizing pipelines.

Get-Content -Path .\sample.json -Raw | ConvertFrom-Json | 
    ForEach-Object { $_ | ConvertTo-Json | Out-File -FilePath "$($_.Status).json" }

Breakdown

Extract JSON data using Get-Content, ensuring the usage of the -Raw parameter to receive the data as a single string while preserving line breaks instead of an array of strings(default behavior).
Pipe the data into ConvertFrom-Json for deserialization into a
```
System.Management.Automation.PSCustomObject
```
.
Iterate through the objects using Foreach-Object and convert each object into a JSON file using ConvertTo-Json along with Out-File. Note that the default depth for ConvertTo-Json is 2, so adjust the depth using the -Depth parameter if your JSON files have deeper levels.

If it helps with clarity, a traditional foreach loop can also be utilized:

$json = Get-Content -Path .\sample.json -Raw | ConvertFrom-Json

foreach ($object in $json) {
    $object | ConvertTo-Json | Out-File -FilePath "$($object.Status).json"
}

Separating JSON objects by their key value

Answer №1

Answer №2

Answer №3

Similar questions

Can you explain the concept of being "well-typed" in TypeScript?

Transforming Thomas J Bradley's signature pad JSON into a PNG file using C# programming language

Accessing the Key Name of a JSON Array in Android Using Dynamic Methods

Are all of the User columns in my database on Parse.com marked as undefined?

Leveraging AJAX for connectivity to the Twitter API

Using Jquery to send json data to a webserver via Ajax (API)

What is the best way to iterate through this array in ajax?

sending a POST request with multiple parts using d3.json() or d3.xhr()

Step-by-step guide on retrieving the 'No User found' message from the specified variable in a Node.js environment

Obtaining a worldwide JavaScript variable through AJAX JSON query

Arranging JSON data by a specific attribute using JavaScript

How to use jQuery to dynamically generate a JSON object within a loop

The current project does not have Json enabled for BIGQUERY

Using jquery and Ajax to extract data from nested JSON structures

Show a dynamic dropdown box using AJAX if a specific variable is present

Generate a JSON file in accordance with a specified template

How can I asynchronously parse JSON data from a URL on a Windows phone and display it in a

Calculate the number of days required for the value in an item to multiply by two

Transforming objects into JSON format using C#

Capturing information from deeply nested JSON structures using Pandas