Convertfrom-Json not working

I’m working on a unique project. I noticed a couple of my runs that I logged on Garmin didn’t sync over to my Runkeeper account like they should have. So I figured I could use Powershell to compare both places and point out the differences. Runkeeper activities can be exported as a csv, so no issue there. Garmin Activities get exported as a Json.

When I attempt to convert them, rather than showing up as nice clean Powershell objects, I get a weird hash table looking thing. Since I haven’t used convert-json much I started working with sample json files that are much shorter and for the most part they work without issue. But I did find one that produces similar output that is much shorter.

I’ve saved this as a file named testingbooks.json

{
   "book": [

      {
         "id": "01",
         "language": "Java",
         "edition": "third",
         "author": "Herbert Schildt"
      },

      {
         "id": "07",
         "language": "C++",
         "edition": "second",
         "author": "E.Balagurusamy"
      }

   ]
}

And this is what happens when I import it.

$path = ".\testingbooks.json"
Get-Content $path | ConvertFrom-Json

book                                                                                                                           
----                                                                                                                           
{@{id=01; language=Java; edition=third; author=Herbert Schildt}, @{id=07; language=C++; edition=second; author=E.Balagurusamy}}

I’m on PS version 5.1 but did see that version 7 has additional parameters for convertfrom-json, so I installed it but still wasn’t able to get anywhere. https://jsonlint.com/ and JSON To CSV Converter confirm that both my sample json as well as the real garmin json files are valid.

Noticing that this comes back as a Pscustom object with a book property



   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition                    
----        ----------   ----------                    
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()             
GetType     Method       type GetType()                
ToString    Method       string ToString()             
book        NoteProperty Object[] book=System.Object[] 

I tried this

Get-Content $path | ConvertFrom-Json | select -expand book

This does what I would expect it to do. And this same method works for the gear Json (with a different property). But it doesn’t work for the activities. Get-Member says it’s a generic object and doesn’t give me access to any properties, despite looking like a summarizedActivitesExport property should exist.

Get-Content $path | ConvertFrom-Json 

summarizedActivitiesExport                                                                                                                                                
--------------------------                                                                                                                                                
{@{activityId=8049344189; uuidMsb=-6130568482093287288; uuidLsb=-1450207411437045759; name=Minneapolis Running; activityType=running; userProfileId=44410958; timeZoneI...
Get-Content $path | ConvertFrom-Json | select -Expand summarizedActivitiesExport
select : Property "summarizedActivitiesExport" cannot be found.
At line:1 char:40
+ ...  $path | ConvertFrom-Json | select -Expand summarizedActivitiesExport
+                                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:PSObject) [Select-Object], PSArgumentException
    + FullyQualifiedErrorId : ExpandPropertyNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand
Get-Content $path | ConvertFrom-Json | gm


   TypeName: System.Object[]

Name           MemberType            Definition                                                                                                                           
----           ----------            ----------                                                                                                                           
Count          AliasProperty         Count = Length                                                                                                                       
Add            Method                int IList.Add(System.Object value)                                                                                                   
Address        Method                System.Object&, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=c77a5c561934e085 Address(int )                            
Clear          Method                void IList.Clear()                                                                                                                   
Clone          Method                System.Object Clone(), System.Object ICloneable.Clone()                                                                              
CompareTo      Method                int IStructuralComparable.CompareTo(System.Object other, System.Collections.IComparer comparer)                                      
Contains       Method                bool IList.Contains(System.Object value)                                                                                             
CopyTo         Method                void CopyTo(array array, int index), void CopyTo(array array, long index), void ICollection.CopyTo(array array, int index)           
Equals         Method                bool Equals(System.Object obj), bool IStructuralEquatable.Equals(System.Object other, System.Collections.IEqualityComparer comparer) 
Get            Method                System.Object Get(int )                                                                                                              
GetEnumerator  Method                System.Collections.IEnumerator GetEnumerator(), System.Collections.IEnumerator IEnumerable.GetEnumerator()                           
GetHashCode    Method                int GetHashCode(), int IStructuralEquatable.GetHashCode(System.Collections.IEqualityComparer comparer)                               
GetLength      Method                int GetLength(int dimension)                                                                                                         
GetLongLength  Method                long GetLongLength(int dimension)                                                                                                    
GetLowerBound  Method                int GetLowerBound(int dimension)                                                                                                     
GetType        Method                type GetType()                                                                                                                       
GetUpperBound  Method                int GetUpperBound(int dimension)                                                                                                     
GetValue       Method                System.Object GetValue(Params int[] indices), System.Object GetValue(int index), System.Object GetValue(int index1, int index2), S...
IndexOf        Method                int IList.IndexOf(System.Object value)                                                                                               
Initialize     Method                void Initialize()                                                                                                                    
Insert         Method                void IList.Insert(int index, System.Object value)                                                                                    
Remove         Method                void IList.Remove(System.Object value)                                                                                               
RemoveAt       Method                void IList.RemoveAt(int index)                                                                                                       
Set            Method                void Set(int , System.Object )                                                                                                       
SetValue       Method                void SetValue(System.Object value, int index), void SetValue(System.Object value, int index1, int index2), void SetValue(System.Ob...
ToString       Method                string ToString()                                                                                                                    
Item           ParameterizedProperty System.Object IList.Item(int index) {get;set;}                                                                                       
IsFixedSize    Property              bool IsFixedSize {get;}                                                                                                              
IsReadOnly     Property              bool IsReadOnly {get;}                                                                                                               
IsSynchronized Property              bool IsSynchronized {get;}                                                                                                           
Length         Property              int Length {get;}                                                                                                                    
LongLength     Property              long LongLength {get;}                                                                                                               
Rank           Property              int Rank {get;}                                                                                                                      
SyncRoot       Property              System.Object SyncRoot {get;}  

Reading through forums, it sounds like many people have had issues with convertfrom-json not behaving expectedly on json files that jsonlint deems are valid files, but I haven’t seen anyone with my same issue. Any suggestions?

What is it what you’re actually trying to do? CSV data are structured data and JSON data are hierarchical data. So you need to use different approaches to deal with them. You may share your actual JSON data if you can and what part you’re interested in and we might be able to help.

Your shared JSON data converted to PS objects can be accessed with the so called dot notation like this:

$JSONInput = @'
{
   "book": [

      {
         "id": "01",
         "language": "Java",
         "edition": "third",
         "author": "Herbert Schildt"
      },

      {
         "id": "07",
         "language": "C++",
         "edition": "second",
         "author": "E.Balagurusamy"
      }

   ]
}
'@ |
    ConvertFrom-Json

$JSONInput.book

What provides the output like this:

id language edition author
-- -------- ------- ------
01 Java     third   Herbert Schildt
07 C++      second  E.Balagurusamy

And of course you can access individual elements from lists with their index … like this:

$JSONInput.book[1].author