Need help regarding calling rest api and getting data

Hi,

I am trying to call a API to fetch the information. I am getting the response in following manner.

status cols output


 1 {User ID, Candidate Name, Candidate ID, Company…} {464120 test user  My company, Inc. IT Systems (IT SYS-1) Application Admin (IT SystemsIT SYS-1Application Admin) Pavillion, Pune,Pune, Maharashtra, India 27-07-2022 27-07-2022 11:05… 

How do I fetch the information against each column?

Assuming you are looking at Content of Invoke-WebRequest? You need to use ConvertFrom-Json to parse into a PSObject. If you use Invoke-RestMethod, it will automatically parse into a PSObject.

1 Like

Prashant,
Welcome to the forum. :wave:t4:

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

… seems like you tried successfully …

… some APIs allow to request information in a particular format. You may try to figure out if the API can provide the information in a format easier to handle. … maybe CSV, or JSON.

How do you request the information from your API?

I am using following command

$response = Invoke-RestMethod -Uri $api_URL -Method ‘Post’ -Headers $Headers -Body ($req_body | ConvertTo-Json)

and when i echo $response i get the output in above format as explained.

Are you setting the ContentType in your header? It should be ‘application/json’. If it’s not in your header, set it in your command -ContentType ‘application/json’. The output you provided is in an odd format. What does the documentation state is the response output? This is typically XML or JSON, but is likely JSON.

I can see the Content-Type as application/json in the API documentation.

In the API documentation, I can see below example given as the output.

{
  "status": 1,
  "cols": [
    "User ID",
    "Candidate Name",
    "Candidate ID",
    "Company",
    "Department",
    "Designation",
    "Office Location",
    "Date of Joining",
    "Date and Time of Deletion",
    "Deleted By",
    "Deleted Reason"
  ],
  "output": [
    [
      "15880",
      "YES BANK TEST ",
      "ABC383",
      "Test2",
      "Department2 (CHILD)",
      "Ass.Director (DES_92)",
      "Ongole, Andhra Pradesh, India, (Previous Location Type)",
      "09-03-2022",
      "27-03-2022 11:06:51",
      "Ashoks CM (vineet)",
      ""
    ],
    [
      "15700",
      "Timesheet Emp4",
      "tse4",
      "Test2",
      "Test (Test_Dep)",
      "QA Engineer (QA1)",
      "Abcd12,Hyderabad, Telangana, India, ()",
      "01-01-2020",
      "27-03-2022 11:08:01",
      "Ashoks CM (vineet)",
      ""
    ]
  ],
  "message": "Deleted Users Loaded Successfully"
}

So are you or have you tried setting it in the call like Rob suggests?

If that is the format, it is not a standard return and the objects need to be manually created. The API is returning a column array and an array (object) of arrays (properties). Try something like this:

$json = @' 
{
    "status": 1,
    "cols": [
      "User ID",
      "Candidate Name",
      "Candidate ID",
      "Company",
      "Department",
      "Designation",
      "Office Location",
      "Date of Joining",
      "Date and Time of Deletion",
      "Deleted By",
      "Deleted Reason"
    ],
    "output": [
      [
        "15880",
        "YES BANK TEST ",
        "ABC383",
        "Test2",
        "Department2 (CHILD)",
        "Ass.Director (DES_92)",
        "Ongole, Andhra Pradesh, India, (Previous Location Type)",
        "09-03-2022",
        "27-03-2022 11:06:51",
        "Ashoks CM (vineet)",
        ""
      ],
      [
        "15700",
        "Timesheet Emp4",
        "tse4",
        "Test2",
        "Test (Test_Dep)",
        "QA Engineer (QA1)",
        "Abcd12,Hyderabad, Telangana, India, ()",
        "01-01-2020",
        "27-03-2022 11:08:01",
        "Ashoks CM (vineet)",
        ""
      ]
    ],
    "message": "Deleted Users Loaded Successfully"
  }
'@ | ConvertFrom-Json

$results = foreach ($item in $json.output) {
     $record = @{}

     for ($i = 0;$i -lt $json.cols.Count;$i++) {
         Write-Host $i
         $record[$json.cols[$i]] = $item[$i]
     }
     
     [pscustomobject]$record
}

$results

Normal JSON would look like this object ($results) converted and would be automatically parsed into a PSObject, like this:

[
  {
    "Date and Time of Deletion": "27-03-2022 11:06:51",
    "Candidate Name": "YES BANK TEST ",
    "User ID": "15880",
    "Department": "Department2 (CHILD)",
    "Deleted By": "Ashoks CM (vineet)",
    "Date of Joining": "09-03-2022",
    "Company": "Test2",
    "Deleted Reason": "",
    "Candidate ID": "ABC383",
    "Designation": "Ass.Director (DES_92)",
    "Office Location": "Ongole, Andhra Pradesh, India, (Previous Location Type)"
  },
  {
    "Date and Time of Deletion": "27-03-2022 11:08:01",
    "Candidate Name": "Timesheet Emp4",
    "User ID": "15700",
    "Department": "Test (Test_Dep)",
    "Deleted By": "Ashoks CM (vineet)",
    "Date of Joining": "01-01-2020",
    "Company": "Test2",
    "Deleted Reason": "",
    "Candidate ID": "tse4",
    "Designation": "QA Engineer (QA1)",
    "Office Location": "Abcd12,Hyderabad, Telangana, India, ()"
  }
]
1 Like

You are amazing @rob-simmers. It indeed solved my issue.

Hi, Yes Rob’s solution worked for me.