ConvertFrom-Json : Cannot convert the JSON string because a dictionary that was converted from the string contains the duplicated keys

Question

The following JSON is getting returned from OData API service:

{
  "d": {
    "results": [
      {
        "FileSystemObjectType": 0,
        "Id": 1,
        "ContentTypeId": "0x0100BC97B2F575CB0C42B79549F3BABD32A8",
        "Title": "Nokia California",
        "Address": "200 South Matilda Avenue\nW Washington Ave\n94086 Sunnyvale, California\nUnited States of America",
        "ID": 1,
        "Modified": "2014-02-24T10:06:39Z",
        "Created": "2014-02-24T10:06:39Z",
        "AuthorId": 12,
        "EditorId": 12,
        "OData__UIVersionString": "1.0",
        "Attachments": false,
        "GUID": "d12aafad-502a-4968-a69e-36a7ea05ec80"
      }
    ]
  }
}

and saved as a string into variable named $data

An attempt to convert a JSON-formatted string to a custom object using ConvertFrom-Json cmdlet:

$results = $data | ConvertFrom-Json

gives the following error:

ConvertFrom-Json : Cannot convert the JSON string because a dictionary that was converted from the string contains the duplicated keys 'Id' and 'ID'.

Is there any way to convert the specified JSON-formatted string in PowerShell?

Answer 1

This is how I have done with it:

$results = $data.ToString().Replace("ID", "_ID") | ConvertFrom-Json

Answer 2

In PowerShell V1.0, or in PowerShell V2.0 when the JSON is too big, I still use a convertion to XML :

Add-Type -AssemblyName System.ServiceModel.Web, System.Runtime.Serialization
function Convert-JsonToXml
{
  PARAM([Parameter(ValueFromPipeline=$true)][string[]]$json)

  BEGIN
  { 
    $mStream = New-Object System.IO.MemoryStream 
  }

  PROCESS
  {
    $json | Write-String -stream $mStream
  }

  END
  {
    $mStream.Position = 0
    try
    {
       $jsonReader = [System.Runtime.Serialization.Json.JsonReaderWriterFactory]::CreateJsonReader($mStream,[System.Xml.XmlDictionaryReaderQuotas]::Max)
       $xml = New-Object Xml.XmlDocument
       $xml.Load($jsonReader)
       $xml
    }
    finally
    {
       $jsonReader.Close()
       $mStream.Dispose()
    }
  }
}

Using this code you can loop thru your items you can test :

$a = Get-Content C:\temp\jsontest.txt
$b.root.d.results.Item
$b.root.d.results.Item[7].Id[0].InnerText

---

(Edited)

In you case I would only replace the expected duplicate ID/Id

$data = Get-Content C:\temp\jsontest.txt -Raw
$datacorrected = $a -creplace '"Id":','"Id-minus":'
$psJsonIn = $datacorrected | ConvertFrom-Json

If really you've got unexpected duplicate you can write a function that trap the convertion error due to duplicated key and replace one.

Answer 3

ConvertFrom-JSON it going to try to create a PS Custom Object from the JSON string. PowerShell object property names are case-insensitive, so "ID" and "id" represent the same property name. You're going to have to do something with those duplicate property names in your JSON before you try to do that conversion, or it's going to fail.