none
Parsing nested JSON RRS feed

  • Question

  • Hello,

    I am parsing the JSON response data (got from Invoke-RestMethod). I would like to put the output into a table, selecting only the necessary columns. However, one of the column that I want to have in the table is nested in JSON.

    When I use: 

    Format-Table -Property id, name, parent
    I get:

         id name                                    parent
         -- ----                                    ------
       6584 Jack                            @{id=1560; name=Mary}
       1563 Mark                            @{id=805; name=John}

    Could you please help me how to have just Mary and John in the parent column?

    Regards

    Monday, August 26, 2013 10:31 AM

Answers

  • This should work:

    Format-Table -Property id,name,@{Label = "parent"; Expression = { $_.parent.name }}
    

    • Marked as answer by neq1337 Monday, August 26, 2013 12:16 PM
    Monday, August 26, 2013 11:38 AM

All replies

  • Try this:

    Format-Table -expand parent


    ¯\_(ツ)_/¯

    Monday, August 26, 2013 10:45 AM
  • Try this:

    Format-Table -expand parent


    ¯\_(ツ)_/¯


    Thank you for quick reply, but the there are only 3 values valid for the -expand parameter (coreonly, enumonly, both), so -expand parent does not work...
    Monday, August 26, 2013 10:57 AM
  • This should work:

    Format-Table -Property id,name,@{Label = "parent"; Expression = { $_.parent.name }}
    

    • Marked as answer by neq1337 Monday, August 26, 2013 12:16 PM
    Monday, August 26, 2013 11:38 AM
  • This should work:

    Format-Table -Property id,name,@{Label = "parent"; Expression = { $_.parent.name }}
    


    Thank you! That works perfectly. However, when trying to export it to CSV using Export-CSV I have to use the Select-Object instead of Format-Table. This parameter does not show the data correctly though (only one line is visible). Is there any other way of exporting this output to CSV?
    Monday, August 26, 2013 11:54 AM
  • The Format-* can only be piped to the Out-* cmdlets.  Everything else (Export-Csv, etc) will produce a bunch of garbage.  As you say, though, you can use Select-Object (or several other methods of creating a PSCustomObject) to keep the pipeline alive:

    Select-Object -Property id,name,@{Name = "parent"; Expression = { $_.parent.name }} |
    Export-Csv -Path 'c:\path\to\file.csv'

    Monday, August 26, 2013 1:30 PM
  • That is exactly the code I had used, but when Format-Table outputs id, name and parent correctly, Select-Object outputs blank name and parent is put in one line.
    Monday, August 26, 2013 1:38 PM
  • Interesting.  I couldn't see, based on what was posted so far, why you'd be having a problem with only getting one row in the CSV file.  This is a little bit of guesswork, since I can't see your complete code, but I think it's a problem with the ConvertFrom-Json command.  Unlike other cmdlets that output objects one at a time, its output was a single object (which happened to be an array).  Piping ConvertFrom-Json straight to things like ForEach-Object or Select-Object doesn't work, in that case.

    That might be a bug, since it doesn't really match the behavior of other cmdlets, but here's how you can work around it.  On my computer, I created a "test.txt" file with JSON notation of the two sample objects in your original post:

    <#
    Test.txt contents:
    
    [
        {
            "parent":  {
                           "id":  1560,
                           "name":  "Mary"
                       },
            "name":  "Jack",
            "id":  6584
        },
        {
            "parent":  {
                           "id":  805,
                           "name":  "John"
                       },
            "name":  "Mark",
            "id":  1563
        }
    ]
    #>
    
    # Note:  The -Raw switch to Get-Content is only available
    # in PowerShell v3.  It appears to be necessary, though,
    # because ConvertFrom-Json won't handle being piped one
    # line at a time.
    
    $objects = Get-Content -Path .\test.txt -Raw |
    ConvertFrom-Json
    
    $objects |
    Select-Object -Property ID, Name, @{Name = 'Parent'; Expression = { $_.Parent.Name }} |
    Export-Csv -Path .\test.csv
    
    # The above commands worked properly, where the following had the problem you described:
    
    <#
    
    Get-Content -Path .\test.txt -Raw |
    ConvertFrom-Json |
    Select-Object -Property ID, Name, @{Name = 'Parent'; Expression = { $_.Parent.Name }} |
    Export-Csv -Path .\test.csv
    
    #>

    Monday, August 26, 2013 1:48 PM
  • You are right. The line-by-line script with variables works, whereas the one-line does not. Thanks a lot!
    Monday, August 26, 2013 2:20 PM
  • Try this:

    Format-Table -expand parent


    ¯\_(ツ)_/¯

    You got me all excited to see if this was a solution to my problem with parsing JSON from Spotlight content

    c.f.

    https://answers.microsoft.com/en-us/windows/forum/all/what-is-todays-windows-login-backround-picture/7a2aac0f-836a-4fe2-a157-3a277665ed1d 

    PS>r 188
    ($J | ConvertFrom-Json).batchrsp.items
    
    item
    ----
    {"f":"raf","v":"1.0","rdr":[{"c":"CDM","u":"SubscribedContent"}],"ad":{"class":"content","collections":[],"name":"LockScreen","propertyManifest":{"landscapeImage":{"type":"image"},"portraitImage":{"type":"imag...
    {"f":"raf","v":"1.0","rdr":[{"c":"CDM","u":"SubscribedContent"}],"ad":{"class":"content","collections":[],"name":"LockScreen","propertyManifest":{"landscapeImage":{"type":"image"},"portraitImage":{"type":"imag...
    {"f":"raf","v":"1.0","rdr":[{"c":"CDM","u":"SubscribedContent"}],"ad":{"class":"content","collections":[],"name":"LockScreen","propertyManifest":{"landscapeImage":{"type":"image"},"portraitImage":{"type":"imag...
    
    
    PS>($J | ConvertFrom-Json).batchrsp.items | ft -expand item
    Format-Table : Cannot validate argument on parameter 'Expand'. The argument "item" does not belong to the set "CoreOnly,EnumOnly,Both" specified by the ValidateSet attribute. Supply an argument that is in the
    set and then try the command again.
    BTW is this thread title too general?   ; )



    Robert Aldwinckle
    ---

    Saturday, October 27, 2018 6:07 PM
  • Should be:

    ($J | ConvertFrom-Json).batchrsp.items | select -expand item

    or

    ($J | ConvertFrom-Json).batchrsp.items.item


    \_(ツ)_/

    Saturday, October 27, 2018 8:04 PM
  • ($J | ConvertFrom-Json).batchrsp.items.item

    I think I had that one myself but it doesn't do what I had hoped.  Do you use Spotlight?  If so, you could try this yourself and see that there is more "nested" parsing that needs to be done to make this idea more useful for its users.  However, when I tried just using another application of ConvertFrom-Json on some of it I found data being lost (or just excluded by the new formatting). 

    For example, I think the most interesting stuff is somewhere in the SubscribedContent thing

    PS>($J | ConvertFrom-Json).batchrsp.items[0].item | ConvertFrom-JSON
    
    
    f   : raf
    v   : 1.0
    rdr : {@{c=CDM; u=SubscribedContent}}
    ad  : @{class=content; collections=System.Object[]; name=LockScreen; propertyManifest=; properties=; tracking=; triggers=System.Object[]; itemPropertyManifest=; items=System.Object[]}
    prm : @{_id=WW_128000000000466614_EN-CA; eid=; startTime=2018-02-07T00:10:00; expireTime=2035-12-31T07:59:00; rotationPeriod=82800; requiresNetwork=0; reuseCount=-1; _imp=post:https://arc.msn.com/v3/Delivery/Even
          ts/Impression=&PID=400090979&TID=700260133&CID=128000000000466614&BID=1266971640&PG=PC000P0FR5.0000000IRS&TPID=400090979&REQASID=FA4D68AEEB2E479D8663E9EA8B47AB86&ASID={ASID}&TIME={DATETIME}&SLOT=1&REQT=2018
          0714T191835&MA_Score=2&SegmentHash=&DS_Pids=&DS_PDFS=&DS_Result=&DS_Status=&DS_Index=&BCNT=1&PG=PC000P0FR5.0000000IRS&UNID=338387&MAP_TID=A0664057-1B93-46AA-8ED2-B8496DDCAA79&NCT=1&PN=DA63DF93-3DBC-42AE-A50
          5-B34988683AC7&ASID=FA4D68AEEB2E479D8663E9EA8B47AB86&REQASID=FA4D68AEEB2E479D8663E9EA8B47AB86&ARC=1&EMS=1&LOCALE=EN-CA&COUNTRY=CA&HTD=-1&LANG=4105&DEVLANG=EN&CIP=209.197.147.52&ID=782C3FC8F2D6684B3DEED55FFF
          FFFFFF&OPTOUTSTATE=0&PERSID=0938C8EB00224313B3A2219E43E816A7&GLOBALDEVICEID=6896132298442481&LOCALID=w:D09C8A0C-B73F-2E1A-7DDE-63880F8FE1A5&HTTPS=1&PRODID=00000000-0000-0000-0000-000000000000&DVTP=2&DEVOSVE
          R=10.0.17134.165&DEVOSMAJ=10&DEVOSMIN=0&DEVOSBLD=17134&DEVOSMINBLD=165&LOD=1080&LOH=24&LO=1555426&RAFB=0&MARKETBASEDCOUNTRY=CA&CLR=CDM&CFMT=TEXT%2CIMAGE&SFT=JPEG%2CPNG%2CGIF%2CJPG&H=1&W=1&TP=1&FESVER=1.3&WP
          X=1&HPX=1&TIME=20180714T191834Z&PL=EN-CA%2CEN-US&CTMODE=MULTISESSION&ARCH=X64&CDMVER=10.0.17134.1&DEVFAM=WINDOWS.DESKTOP&DEVFORM=UNKNOWN&DISPHORZRES=1920&DISPSIZE=23.1&DISPVERTRES=1080&FOSVER=15063&ISU=0&ME
          TERED=FALSE&NETTYPE=ETHERNET&NPID=SC-338387&OEMNAME=MICROSOFT%20CORPORATION&OEMID=MICROSOFT_CORPORATION&OSSKU=PROFESSIONAL&PREVOSVER=10586&RVER=2&SC-MODE=0&SMBIOSDM=SURFACE%20PRO%202&TL=4&TSU=5548&WAASBLDFL
          T=1&WAASCFGEXP=1&WAASCFGSET=1&WAASRETAIL=1&WAASRING=&ARCRAS=1&CHNL=CFD&UIT=A}
    
    



    Robert Aldwinckle
    ---

    Sunday, October 28, 2018 12:12 AM
  • Yes.  That is why we have scripting.  You have to write a custom script that gets what you wan the way you want it.


    \_(ツ)_/

    Sunday, October 28, 2018 12:14 AM