locked
Looking for examples Powershell convertFrom-json where there are multiple arrays RRS feed

  • Question

  • Trying to parse "complex" json to csv for eventual import to Access database. Just found Powershell. Have searched forums and youtube but examples have no arrays.  I would like to find some examples to help with the learning/experience.

    I do not have any Powershell experience currently.

    I have found posts by jrv that help some, but a push to an example would be helpful.

    Thanks in advance.

    jdraw   Win 10 desktop  Powershell 5

    • Moved by Just Karl Wednesday, September 20, 2017 1:38 PM Looking for the correct forum
    Monday, September 18, 2017 11:38 PM

Answers

  • $json = @'
    [{
    	"squadName": "Super hero squad Alpha",
    	"homeTown": "Metro City",
    	"formed": 2016,
    	"secretBase": "Large tent in the forest",
    	"active": "True",
    	"members": [{
    		"name": "Molecule Man",
    		"age": 29,
    		"secretIdentity": "Dan Jukes",
    		"powers": ["Radiation resistance",
    		"Turning tiny",
    		"Radiation blast"]
    	},
    	{
    		"name": "Madame Uppercut",
    		"age": 39,
    		"secretIdentity": "Jane Wilson",
    		"powers": ["Million tonne punch",
    		"Damage resistance",
    		"Superhuman reflexes"]
    	},
    	{
    		"name": "Eternal Flame",
    		"age": 1000000,
    		"secretIdentity": "Unknown",
    		"powers": ["Immortality",
    		"Heat Immunity",
    		"Inferno",
    		"Teleportation",
    		"Interdimensional travel"]
    	}]
    },
    {
    	"squadName": "Second squad Baker",
    	"homeTown": "Metro Toronto",
    	"formed": 2017,
    	"secretBase": "CN tower",
    	"active": "True",
    	"members": [{
    		"name": "Kathleen Wynne",
    		"age": 49,
    		"secretIdentity": "Cyan Arrah",
    		"powers": ["XRay vision",
    		"Invisibility",
    		"Radiation blast"]
    	},
    	{
    		"name": "Madame Butterfly",
    		"age": 27,
    		"secretIdentity": "Iman Angel",
    		"powers": ["Magical hearing",
    		"Fantastic ideas"]
    	},
    	{
    		"name": "Gassy Misty Cloud",
    		"age": 1000,
    		"secretIdentity": "Puff of Smoke",
    		"powers": ["Immortality",
    		"Heat and Flame Immunity",
    		"Impeccable hearing",
    		"Xray Vision",
    		"Able to jump tall buildings",
    		"Teleportation",
    		"Intergalactic travel"]
    	}]
    }]
    '@
    $json | ConvertFrom-Json
    $squads = $json | ConvertFrom-Json
    

    Produces this:


    \_(ツ)_/

    • Marked as answer by jdrawMVP Wednesday, September 20, 2017 2:14 PM
    • Unmarked as answer by jdrawMVP Wednesday, September 20, 2017 4:10 PM
    • Marked as answer by jdrawMVP Tuesday, September 26, 2017 1:27 PM
    Wednesday, September 20, 2017 1:53 PM
  • Unfortunately your asking more than one question and your outcome is not clear.  The answers to your questions lie in understanding how Json is converted into objects and how PowerShell works with objects.  For each complex Json object you will have to understand the object then apply what you know about PowerShell to "convert" it into tables.  My example shows the basic method.  There is not single way to do this for all Json.

    \_(ツ)_/

    • Marked as answer by jdrawMVP Tuesday, September 26, 2017 7:20 PM
    Tuesday, September 26, 2017 6:03 PM

All replies

  • Hi,

    Checkout this link

    https://gist.github.com/dfinke/2444aef8ebee03db708d

    Tuesday, September 19, 2017 12:57 AM
  • Thanks for the link. My situation is a little different in that

    -I have Squads with names and some attributes

    -each Squad has Members with names, age , identify

    -each Member has a number of Powers

    so the json has embedded arrays, eg

    Squad

    ------Squad member

    ----------Squad member Power

    Here is the test json file I am trying to parse

    [{
        "squadName": "Super hero squad Alpha",
        "homeTown": "Metro City",
        "formed": 2016,
        "secretBase": "Large tent in the forest",
        "active": "True",
        "members": [{
            "name": "Molecule Man",
            "age": 29,
            "secretIdentity": "Dan Jukes",
            "powers": ["Radiation resistance",
            "Turning tiny",
            "Radiation blast"]
        },
        {
            "name": "Madame Uppercut",
            "age": 39,
            "secretIdentity": "Jane Wilson",
            "powers": ["Million tonne punch",
            "Damage resistance",
            "Superhuman reflexes"]
        },
        {
            "name": "Eternal Flame",
            "age": 1000000,
            "secretIdentity": "Unknown",
            "powers": ["Immortality",
            "Heat Immunity",
            "Inferno",
            "Teleportation",
            "Interdimensional travel"]
        }]
    },
    {
        "squadName": "Second squad Baker",
        "homeTown": "Metro Toronto",
        "formed": 2017,
        "secretBase": "CN tower",
        "active": "True",
        "members": [{
            "name": "Kathleen Wynne",
            "age": 49,
            "secretIdentity": "Cyan Arrah",
            "powers": ["XRay vision",
            "Invisibility",
            "Radiation blast"]
        },
        {
            "name": "Madame Butterfly",
            "age": 27,
            "secretIdentity": "Iman Angel",
            "powers": ["Magical hearing",
            "Fantastic ideas"]
        },
        {
            "name": "Gassy Misty Cloud",
            "age": 1000,
            "secretIdentity": "Puff of Smoke",
            "powers": ["Immortality",
            "Heat and Flame Immunity",
            "Impeccable hearing",
            "Xray Vision",
            "Able to jump tall buildings",
            "Teleportation",
            "Intergalactic travel"]
        }]
    }]


    This is what I have tried and the results.

    PS C:\Users\mellon> $file = "C:\Users\mellon\documents\ridders\files\multisquad.json"
    $json = ConvertFrom-JSON (Get-Content $file -Raw)

    PS C:\Users\mellon> $json


    squadName  : Super hero squad Alpha
    homeTown   : Metro City
    formed     : 2016
    secretBase : Large tent in the forest
    active     : True
    members    : {@{name=Molecule Man; age=29; secretIdentity=Dan Jukes; powers=System.Object[]}, @{name=Madame Uppercut; age=39; secretIdentity=Jane Wilson; powers=System.Object[]},
                 @{name=Eternal Flame; age=1000000; secretIdentity=Unknown; powers=System.Object[]}}

    squadName  : Second squad Baker
    homeTown   : Metro Toronto
    formed     : 2017
    secretBase : CN tower
    active     : True
    members    : {@{name=Kathleen Wynne; age=49; secretIdentity=Cyan Arrah; powers=System.Object[]}, @{name=Madame Butterfly; age=27; secretIdentity=Iman Angel;
                 powers=System.Object[]}, @{name=Gassy Misty Cloud; age=1000; secretIdentity=Puff of Smoke; powers=System.Object[]}}
    ==============================================================

    And then (based on the link you gave)

    PS C:\Users\mellon> $m=$json.members.powers.PSObject.Properties | foreach-object {
        $name = $_.Name
        $value = $_.value
        "$name = $value"
    }

    PS C:\Users\mellon> $m
    Count = 23
    Length = 23
    LongLength = 23
    Rank = 1
    SyncRoot = Radiation resistance Turning tiny Radiation blast Million tonne punch Damage resistance Superhuman reflexes Immortality Heat Immunity Inferno Teleportation Interdimensiona
    l travel XRay vision Invisibility Radiation blast Magical hearing Fantastic ideas Immortality Heat and Flame Immunity Impeccable hearing Xray Vision Able to jump tall buildings Telep
    ortation Intergalactic travel
    IsReadOnly = False
    IsFixedSize = True
    IsSynchronized = False

    • Edited by jdrawMVP Tuesday, September 19, 2017 2:19 AM
    Tuesday, September 19, 2017 1:58 AM
  • Hello,

    The Windows Desktop Perfmon and Diagnostic tools forum is to discuss performance monitor (perfmon), resource monitor (resmon), and task manager, focusing on HOW-TO, Errors/Problems, and usage scenarios.

    As the question is off topic here, I am moving it to the Windows PowerShell forum.

    However, also look in the script center.

     Karl

    Wednesday, September 20, 2017 1:37 PM
  • Thanks Karl. I didn't see a Windows Powershell option.
    Wednesday, September 20, 2017 1:44 PM
  • You cannot "subedit" objects.  Try describing what you are trying to do.  Your post does not make any of this clear.


    \_(ツ)_/

    Wednesday, September 20, 2017 1:47 PM
  • $json = @'
    [{
    	"squadName": "Super hero squad Alpha",
    	"homeTown": "Metro City",
    	"formed": 2016,
    	"secretBase": "Large tent in the forest",
    	"active": "True",
    	"members": [{
    		"name": "Molecule Man",
    		"age": 29,
    		"secretIdentity": "Dan Jukes",
    		"powers": ["Radiation resistance",
    		"Turning tiny",
    		"Radiation blast"]
    	},
    	{
    		"name": "Madame Uppercut",
    		"age": 39,
    		"secretIdentity": "Jane Wilson",
    		"powers": ["Million tonne punch",
    		"Damage resistance",
    		"Superhuman reflexes"]
    	},
    	{
    		"name": "Eternal Flame",
    		"age": 1000000,
    		"secretIdentity": "Unknown",
    		"powers": ["Immortality",
    		"Heat Immunity",
    		"Inferno",
    		"Teleportation",
    		"Interdimensional travel"]
    	}]
    },
    {
    	"squadName": "Second squad Baker",
    	"homeTown": "Metro Toronto",
    	"formed": 2017,
    	"secretBase": "CN tower",
    	"active": "True",
    	"members": [{
    		"name": "Kathleen Wynne",
    		"age": 49,
    		"secretIdentity": "Cyan Arrah",
    		"powers": ["XRay vision",
    		"Invisibility",
    		"Radiation blast"]
    	},
    	{
    		"name": "Madame Butterfly",
    		"age": 27,
    		"secretIdentity": "Iman Angel",
    		"powers": ["Magical hearing",
    		"Fantastic ideas"]
    	},
    	{
    		"name": "Gassy Misty Cloud",
    		"age": 1000,
    		"secretIdentity": "Puff of Smoke",
    		"powers": ["Immortality",
    		"Heat and Flame Immunity",
    		"Impeccable hearing",
    		"Xray Vision",
    		"Able to jump tall buildings",
    		"Teleportation",
    		"Intergalactic travel"]
    	}]
    }]
    '@
    $json | ConvertFrom-Json
    $squads = $json | ConvertFrom-Json
    

    Produces this:


    \_(ツ)_/

    • Marked as answer by jdrawMVP Wednesday, September 20, 2017 2:14 PM
    • Unmarked as answer by jdrawMVP Wednesday, September 20, 2017 4:10 PM
    • Marked as answer by jdrawMVP Tuesday, September 26, 2017 1:27 PM
    Wednesday, September 20, 2017 1:53 PM
  • jrv,

    I am hoping to use Powershell to parse json and to import the result into an MSAccess table (or possibly multi tables). There is no ConvertFrom-json, or import from json utility etc within MSAccess.

    This started with a  member(different forum)working with Access and web services with XML and then some of the services updated to json. He was having difficulty and asked about Access and json.

    In my test json example, there are squads. Each squad can have 1 or more members. Each member can have 0 or more Powers. In Access/database the construct would be 3 related tables.

    I would like to know - how to get the details for each member from the json, as below where the {@.... shows  attributes for each member; and also how to do similar for the powers.

    members    : {@{name=Molecule Man; age=29; secretIdentity=Dan Jukes; powers=System.Object[]}, @{name=Madame Uppercut; age=39; secretIdentity=Jane Wilson; powers=System.Object[]},
                 @{name=Eternal Flame; age=1000000; secretIdentity=Unknown; powers=System.Object[]}}

    In effect, I'd like to be able to parse the json to get

    -the Squad info into csv or similar; the member info that can be related to Squad, and the Power info that can be related to Member. ( I recognize that some manual intervention is likely)

    In general terms, a json file could have embedded arrays (as my sample case does). Does PS have capability to process the embedded arrays and convert to  csv files? Can PS script be invoked using a Shell command from Access/vba?

    As mentioned earlier, I am brand new to PS and have no experience with PS scripting.

    Thanks in advance for looking at this post.

    UPDATE: jrv just saw your latest response. I was posting. Do you have a scripting reference (youtube or other that may help with learning) especially related to json ?

    Still looking for scripting reference materials (videos, tutorials..) to help with json parsing.

    • Edited by jdrawMVP Wednesday, September 20, 2017 4:12 PM
    Wednesday, September 20, 2017 2:09 PM
  • Hi,

    I'm checking how the issue is going, was your issue resolved?

    And if the replies as above are helpful, we would appreciate you to mark them as answers, and if you resolve it using your own solution, please share your experience and solution here. It will be greatly helpful to others who have the same question.

    Appreciate for your feedback.

    Best Regards,
    Albert Ling

    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by jdrawMVP Tuesday, September 26, 2017 1:26 PM
    • Unmarked as answer by jrv Tuesday, September 26, 2017 5:58 PM
    Tuesday, September 26, 2017 9:29 AM
  • Albert,

    The response by jrv helped in that it showed me some syntax and led to some other links/searches.

    However, I have not resolved the detailed parsing of json with multiple subarrays into appropriate csv(s) for import into database.

    So, yes it was an answer, and it was helpful, but it was not a solution. I was hesitant to mark the response as an answer since it was not a solution. Since it was helpful, I will again mark it as an answer.

    Please note I am not trying to be picky. I am new to PS and this forum. I am on forums where people mark a post as SOLVED when in fact it is a response, not a solution.

    My goal with some PS scripting is to take the "complex json" and parse it into files such that 3 "normalized tables" would result, namely Squads, Members and Powers.

    I fully admit and recognize that I am a beginner with PS and this forum. I anyone knowledgeable in PS scripting has a solution, I would appreciate seeing it.

    Thanks.

    Tuesday, September 26, 2017 1:26 PM
  • Unfortunately your asking more than one question and your outcome is not clear.  The answers to your questions lie in understanding how Json is converted into objects and how PowerShell works with objects.  For each complex Json object you will have to understand the object then apply what you know about PowerShell to "convert" it into tables.  My example shows the basic method.  There is not single way to do this for all Json.

    \_(ツ)_/

    • Marked as answer by jdrawMVP Tuesday, September 26, 2017 7:20 PM
    Tuesday, September 26, 2017 6:03 PM
  • Thanks jrv.

    Yes, your response and latest info indicates that there is no single method to handle "complex json". I recognize that now and am trying to learn/find tutorials or sample code to work from the basic method and script other method(s). I have not yet found examples for dealing with the interim/intermediate results and piping/processing further. I did find some youtube videos that are helpful.

    Since PS is new to me and the terminology is different, it will be more trial and error for a while.

    eg json object vs powershell object    similar terms but different things.

    I do appreciate your info.

    Thanks.

    Tuesday, September 26, 2017 7:27 PM
  • The saga continues. I have watched some youtube videos on Powershell, and have found a few examples. Perhaps I can ask my question again with my sample json (as shown earlier).

    ***NOTE*** The  sample json file represents embedded arrays. It is a test case for learning.

    In the example I really want to create 3 csv files for import into MS Access.[Access does not have intrinsic routines to import json. It is the ConvertFrom-JSON that drew my attention to Powershell>)

    The tables would be Squads, SquadMembers and SquadMemberPowers.

    Squads would contain squadname, hometown, active, formed and secretbase

    SquadMembers would contain squadname, age, name, secretIdentity

    SquadMemberPowers would contain (member)name, power.

    I have created a script to identify the components of each table by writing to the screen.

    However, I'd like to write each of these "proposed tables' to separate csv files. I have been unable to get these to respective csv files.

    Here is my latest script, followed by the output. The strings in brackets are simply for display and would not be in the final files.

    #Script to identify the data required for 3 normalized tables from this json
    # squad details, member details related to squad, and powers related to member
    #
    $file = "C:\Users\mellon\documents\ridders\files\multisquad.json"
    $tab = "    "
    $json = ConvertFrom-JSON (Get-Content $file -Raw)
    foreach ($sqd in $json)
    {"squad -" + ($sqd.squadname +"," + $sqd.homeTown + "," + $sqd.secretBase + "," + $sqd.formed + "," + $sqd.active)  | write-output
     foreach ($mem in $sqd.members)
      { 
      $tab +"  (squadname) " + $sqd.squadName + " -(member) " + $mem.name + "," + $mem.age + "," + $mem.secretIdentity
            foreach ($pwr in $mem.powers)
            { 
             $tab +$tab + " (member name) "  +$tab + $mem.name +$tab + " -(power) " +  $pwr
            }
       }
    }
    

    The output is:

    PS C:\WINDOWS\system32> C:\Users\mellon\Documents\PSScripts\MultiSquadA.ps1

    squad -Super hero squad Alpha,Metro City,Large tent in the forest,2016,True
          (squadname) Super hero squad Alpha -(member) Molecule Man,29,Dan Jukes
             (member name)     Molecule Man     -(power) Radiation resistance
             (member name)     Molecule Man     -(power) Turning tiny
             (member name)     Molecule Man     -(power) Radiation blast
          (squadname) Super hero squad Alpha -(member) Madame Uppercut,39,Jane Wilson
             (member name)     Madame Uppercut     -(power) Million tonne punch
             (member name)     Madame Uppercut     -(power) Damage resistance
             (member name)     Madame Uppercut     -(power) Superhuman reflexes
          (squadname) Super hero squad Alpha -(member) Eternal Flame,1000000,Unknown
             (member name)     Eternal Flame     -(power) Immortality
             (member name)     Eternal Flame     -(power) Heat Immunity
             (member name)     Eternal Flame     -(power) Inferno
             (member name)     Eternal Flame     -(power) Teleportation
             (member name)     Eternal Flame     -(power) Interdimensional travel
    squad -Second squad Baker,Metro Toronto,CN tower,2017,True
          (squadname) Second squad Baker -(member) Kathleen Wynne,49,Cyan Arrah
             (member name)     Kathleen Wynne     -(power) XRay vision
             (member name)     Kathleen Wynne     -(power) Invisibility
             (member name)     Kathleen Wynne     -(power) Radiation blast
          (squadname) Second squad Baker -(member) Madame Butterfly,27,Iman Angel
             (member name)     Madame Butterfly     -(power) Magical hearing
             (member name)     Madame Butterfly     -(power) Fantastic ideas
          (squadname) Second squad Baker -(member) Gassy Misty Cloud,1000,Puff of Smoke
             (member name)     Gassy Misty Cloud     -(power) Immortality
             (member name)     Gassy Misty Cloud     -(power) Heat and Flame Immunity
             (member name)     Gassy Misty Cloud     -(power) Impeccable hearing
             (member name)     Gassy Misty Cloud     -(power) Xray Vision
             (member name)     Gassy Misty Cloud     -(power) Able to jump tall buildings
             (member name)     Gassy Misty Cloud     -(power) Teleportation
             (member name)     Gassy Misty Cloud     -(power) Intergalactic travel

    I would appreciate any advice, suggestions.

    Thanks.


    • Edited by jdrawMVP Wednesday, October 4, 2017 7:42 PM
    Wednesday, October 4, 2017 1:05 PM