CODEWITHSUNDEEP
jsonpowershell
Andy Pham
Andy Pham

Reputation: 67

How to retrieve field from json file

Here is my Json file. I would like to retrieve the "uniqueName". I could access json field by jsonobject.value.identity after convertfrom-json; however, when I tried jsonobject.value.identity.uniqueName, it is not working. Please advise. Great appreciate

{
    "value":  [
                  {
                      "identity":  "@{displayName=user name1; _links=; id=b4769e73-9493-4617-92fc-1637e57eb871; uniqueName=domain\\user1; descriptor=win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0xODg0OA}"
                  },
                  {
                      "identity":  "@{displayName=user name2; _links=; id=fe4fcac1-a369-4adc-8e94-218971a150c2; uniqueName=domain\\user2; descriptor=win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0xOTIzNg}"
                  },
                  {
                      "isTeamAdmin":  true,
                      "identity":  "@{displayName=user name3; _links=; id=27a9b487-6ce0-46cd-b1d0-227f3ccae851; uniqueName=domain\\user3; descriptor=win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0zNTEzOA}"
                  },
              ],
    "count":  3
}

Upvotes: 3

Views: 286

Answers (4)

Remko
Remko

Reputation: 7340

The issue is that the identity value is a string holding key/value pairs, separated by ; instead of json properties. If you use the supplied json in a site like https://jsonformatter.org/ you can clearly see this:

enter image description here

You would need to correct the formatting in something that is valid (e.g. make the values quoted) or parse the string yourself.

Here is an example to parse the data:

$json = @"
{
    "value":  [
                  {
                      "identity":  "@{displayName=user name1; _links=; id=b4769e73-9493-4617-92fc-1637e57eb871; uniqueName=domain\\user1; descriptor=win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0xODg0OA}"
                  },
                  {
                      "identity":  "@{displayName=user name2; _links=; id=fe4fcac1-a369-4adc-8e94-218971a150c2; uniqueName=domain\\user2; descriptor=win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0xOTIzNg}"
                  },
                  {
                      "isTeamAdmin":  true,
                      "identity":  "@{displayName=user name3; _links=; id=27a9b487-6ce0-46cd-b1d0-227f3ccae851; uniqueName=domain\\user3; descriptor=win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0zNTEzOA}"
                  }
              ],
    "count":  3
}
"@

# convert json to object
$jsonObject = ConvertFrom-Json -InputObject $json

# loop through the value array
for ($i=0 ; $i -lt $jsonObject.value.identity.Count ; $i++)
{

    # create object to hold the data
    $object = New-Object -TypeName PSObject

    # strip @{ at start and } at end of line, then split on ;
    # and feed the array to ForEach...
    $jsonObject.value[$i].identity -replace '^@{','' -replace '}$','' -split ";" | ForEach-Object { 
        # find the key/value pair w/regex
        if ($_ -match '^(.*)=(.*)')
        {
            # use Trim to remove spaces as the ; is followed by a space
            # and add to object
            $object | Add-Member -MemberType NoteProperty -Name $matches[1].Trim() -Value $matches[2].Trim()
        }
    }

    # replace identity with the new object
    $jsonObject.value[$i].identity = $object    
}

# now you can access uniqueName directly e.g.
$jsonObject.value.identity.uniqueName

# or
$jsonObject.value[0].identity.uniqueName

# you can even convert it back to json:
ConvertTo-Json $jsonObject

Upvotes: 0

js2010
js2010

Reputation: 27473

This looks like what happens when using convertto-json with the default depth of 2 and it's not enough depth:

[pscustomobject]@{name = [pscustomobject]@{name = [pscustomobject]@{name =
  [pscustomobject]@{name = 'joe'}}}} | convertto-json -depth 2

{
  "name": {
    "name": {
      "name": "@{name=joe}"
    }
  }
}

With a depth of 3 in this example it comes out correctly:

[pscustomobject]@{name = [pscustomobject]@{name = [pscustomobject]@{name =
  [pscustomobject]@{name = 'joe'}}}} | convertto-json -depth 3

{
  "name": {
    "name": {
      "name": {
        "name": "joe"
      }
    }
  }
}

Upvotes: 0

Avshalom
Avshalom

Reputation: 8889

Once you converted the json to PSObject using ConvertFrom-Json, you can do a simple split:

$json.value | % {$_.identity.Split('; ') | ? {$_ -match 'uniqueName'}}

uniqueName=domain\user1
uniqueName=domain\user2
uniqueName=domain\user3

Also, you need to remove the , (comma) after the last value item, just before the array ends... so ConvertFrom-Json will work...

Upvotes: 1

Rich Moss
Rich Moss

Reputation: 2384

That's not valid json. There is a comma before the ] that will keep it from being parsed by ConvertFrom-Json. Also there are unquoted strings within the "identity" values. If you corrected those problems you could use Invoke-Expression to evaluate the "identity" and parse it into an object. 

$valueList = @"
{
    "value":  [
                  {
                      "identity":  "@{displayName='user name1'; _links=''; id='b4769e73-9493-4617-92fc-1637e57eb871'; uniqueName='domain\\user1'; descriptor='win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0xODg0OA'}"
                  },
                  {
                      "identity":  "@{displayName='user name2'; _links=''; id='fe4fcac1-a369-4adc-8e94-218971a150c2'; uniqueName='domain\\user2'; descriptor='win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0xOTIzNg'}"
                  },
                  {
                      "isTeamAdmin":  "true",
                      "identity":  "@{displayName='user name3'; _links=''; id='27a9b487-6ce0-46cd-b1d0-227f3ccae851'; uniqueName='domain\\user3'; descriptor='win.Uy0xLTUtMjEtMTA3NTAwMjQyMy0xNjU2OTgyMTMwLTQxNzMzMTg1MC0zNTEzOA'}"
                  }
              ],
    "count":  3
}
"@ | ConvertFrom-Json
 $valueList.value.identity | %{Invoke-Expression "[psobject] $_" } | %{$_.uniqueName }

Do you have access to edit the file, or it's source?

Upvotes: 0

Related Questions