Fetch data from JSON with powershell

Question

I have without success tried to figure out how to most efficiently fetch data with powershell from the below JSON that is returned to me by a REST API:

    {
  "$schema": "api:standardResponse",
  "links": [
    {
      "rel": "canonical",
      "href": "http://localhost:8501/services/v2/replicats",
      "mediaType": "application/json"
    },
    {
      "rel": "self",
      "href": "http://localhost:8501/services/v2/replicats",
      "mediaType": "application/json"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8501/services/v2/metadata-catalog/replicats",
      "mediaType": "application/schema+json"
    }
  ],
  "messages": [],
  "response": {
    "$schema": "ogg:collection",
    "items": [
      {
        "links": [
          {
            "rel": "parent",
            "href": "http://localhost:8501/services/v2/replicats",
            "mediaType": "application/json"
          },
          {
            "rel": "canonical",
            "href": "http://localhost:8501/services/v2/replicats/RNIMDA00",
            "mediaType": "application/json"
          }
        ],
        "$schema": "ogg:collectionItem",
        "name": "RNIMDA00"
      },
      {
        "links": [
          {
            "rel": "parent",
            "href": "http://localhost:8501/services/v2/replicats",
            "mediaType": "application/json"
          },
          {
            "rel": "canonical",
            "href": "http://localhost:8501/services/v2/replicats/RNIMDA01",
            "mediaType": "application/json"
          }
        ],
        "$schema": "ogg:collectionItem",
        "name": "RNIMDA01"
      },
      {
        "links": [
          {
            "rel": "parent",
            "href": "http://localhost:8501/services/v2/replicats",
            "mediaType": "application/json"
          },
          {
            "rel": "canonical",
            "href": "http://localhost:8501/services/v2/replicats/RNIMDA02",
            "mediaType": "application/json"
          }
        ],
        "$schema": "ogg:collectionItem",
        "name": "RNIMDA02"
      }
    ]
  }
}

I only need the data from the "name": node, and the data from the "href": node.

I have done some searching and found som examples where the JSON is converted with ConvertFrom-Json and then iterated with a foreach like the pseudo code below:

$users = $response | ConvertFrom-Json
foreach ($user in $users)

{

write-host "$($user.name) has the email: $($user.email)"

}

But I wonder if there is a better way of fetching data with powershell from a object that contains JSON.

Thanks :)

What problem are you having with the ConvertFrom-Json solution you already found? — jkiiski
– jkiiski, Commented Nov 8, 2022 at 12:49
@jkiiski - I just thought there would be better ways to fetch data from a object with structed data than iterating through it. — abodilsen
– abodilsen, Commented Nov 8, 2022 at 12:58
@Theo - I know. I should have mentioned that I only posted a subset of the full JSON dataset as it would be too large for the post. Thank you for pointing it out — abodilsen
– abodilsen, Commented Nov 8, 2022 at 13:01

Theo · Accepted Answer · 2022-11-08 14:11:35Z

It is not really clear what your desired output should be.

Let's assume you have the JSON data converted in a variable $json using $json = $response | ConvertFrom-Json

Then if you do:

$json.response.items | Select-Object name, @{Name = 'href'; Expression = {$_.links.href}}

You will get objects with a name and a href property, where the href will be an array.

PowerShell would output this on screen as

name     href                                                                                               
----     ----                                                                                               
RNIMDA00 {http://localhost:8501/services/v2/replicats, http://localhost:8501/services/v2/replicats/RNIMDA00}
RNIMDA01 {http://localhost:8501/services/v2/replicats, http://localhost:8501/services/v2/replicats/RNIMDA01}
RNIMDA02 {http://localhost:8501/services/v2/replicats, http://localhost:8501/services/v2/replicats/RNIMDA02}

If however you would like to return an object for each of the href values inside the links nodes you can do:

$json.response.items | ForEach-Object {
    $name = $_.name
    foreach ($link in $_.links) {
        [PsCustomObject]@{
            name = $name
            href = $link.href
       }
    }
}

which will output

name     href                                                
----     ----                                                
RNIMDA00 http://localhost:8501/services/v2/replicats         
RNIMDA00 http://localhost:8501/services/v2/replicats/RNIMDA00
RNIMDA01 http://localhost:8501/services/v2/replicats         
RNIMDA01 http://localhost:8501/services/v2/replicats/RNIMDA01
RNIMDA02 http://localhost:8501/services/v2/replicats         
RNIMDA02 http://localhost:8501/services/v2/replicats/RNIMDA02

Paul-Marie · Accepted Answer · 2022-11-08 12:43:01Z

-1

jq is what you need.

Just install the windows version, add it to your PATH (depending on how you installed it) and use the following command:

curl -s "https://your-url" | jq -r '.response.items.links.href'

answered Nov 8, 2022 at 12:43

Paul-Marie

1,1292 gold badges10 silver badges29 bronze badges

1 Comment

abodilsen Over a year ago

Thank you for your suggestion, but I would have hoped for a more native powershell solution :)

Collectives™ on Stack Overflow

Fetch data from JSON with powershell

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related