Read JSON file from Azure Blob Storage using PowerShell script and write back to another file in blob storage

0
votes

I have following JSON file (product.json) stored in Azure Blob storage. Is it possible to write PowerShell script to read this file from blob storage make some changes and write back to another file. The output file I would like where following changes should occur:

  1. Replace all "_id" with "id"
  2. Remove all "_rev" and their values.

Product.json

[
  {
    "_id": "9f4da9d6babeb9d411c896baa68c94c8",
    "_rev": "1-4259271795225df18768ab68baacc96c",
    "account_id": 692278,
    "limit": 10000,
    "products": [
      "Commodity",
      "InvestmentStock"
    ]
  },
  {
    "_id": "cc4b59f585b8556a2bedca78294a0797",
    "_rev": "1-410e479257faba0457bd9b4816c4dc95",
    "account_id": 328304,
    "limit": 10000,
    "products": [
      "Derivatives",
      "InvestmentStock",
      "CurrencyService"
    ]
  },
  {
    "_id": "d7e2a72963cff2760514ff772969ffe0",
    "_rev": "1-2ec6e2679eae13b76410c93f49c14c4a",
    "account_id": 674364,
    "limit": 10000,
    "products": [
      "InvestmentStock"
    ]
  }
]

The outputfile.json should be as follows:

[
  {
    "id": "9f4da9d6babeb9d411c896baa68c94c8",
    "account_id": 692278,
    "limit": 10000,
    "products": [
      "Commodity",
      "InvestmentStock"
    ]
  },
  {
    "id": "cc4b59f585b8556a2bedca78294a0797",
    "account_id": 328304,
    "limit": 10000,
    "products": [
      "Derivatives",
      "InvestmentStock",
      "CurrencyService"
    ]
  },
  {
    "id": "d7e2a72963cff2760514ff772969ffe0",
    "account_id": 674364,
    "limit": 10000,
    "products": [
      "InvestmentStock"
    ]
  }
]
2
powershellazure-blob-storageazure-powershell
Please edit your question and include what you have tried so far (PS script that you have written) and what are the issues you're running into. – Gaurav Mantri
Thanks Gaurav. Please edited version. – Dinesh Madhup
What version of SDK you're using. Also this is C# code. You mentioned PowerShell. – Gaurav Mantri
SDK 3.0. I was trying C#. But ask was PowerShell if possible. no idea its doable using PowerShell – Dinesh Madhup
I removed C# code which I tried. Lets not confuse other as ask was using PowerShell if possible – Dinesh Madhup

2 Answers

1
votes

Not the most elegant way, but this should do the trick:

$accountName = "account-name"
$accountKey = "account-key"
$containerName = "container-name"
$blobName = "Product.json"
$outputBlobName = "Output.json"

$ctx = New-AzStorageContext -StorageAccountName $accountName -StorageAccountKey $accountKey

#Read blob and save it to local file
Get-AzStorageBlobContent -Blob $blobName -Container $containerName -Destination "Product.json" -Context $ctx

#Read local file and get JSON object
$jsonContent = Get-Content -Raw -Path "Product.json" | ConvertFrom-Json

#Loop through json content and manipulate it
For ($i=0; $i -lt $jsonContent.Length; $i++) {
    $jsonContent[$i] | Add-Member -NotePropertyName "id" -NotePropertyValue $jsonContent[$i]._id
    $jsonContent[$i].PsObject.Properties.Remove("_id")
    $jsonContent[$i].PsObject.Properties.Remove("_rev")
}
$jsonContent

#Write content back to local disk
$jsonContent | ConvertTo-Json | Set-Content -Path $outputBlobName

#Upload into Azure Storage
$properties = @{"ContentType" = "application/json"}
Set-AzStorageBlobContent -File $outputBlobName -Container $containerName -Blob $outputBlobName -Properties $properties -Context $ctx
1
votes

If you want to store the file in memory, you can use the DownloadText() method to download the content into memory.

The sample code:

$accountName = "xxx"
$accountKey = "xxx"
$containerName = "xxx"
$blobName = "Product.json"
$outputBlobName = "Output.json"    

$context = New-AzStorageContext -StorageAccountName $accountName -StorageAccountKey $accountKey
$container_client = Get-AzStorageContainer -Name $containerName -Context $context
$source_blob_client = $container_client.CloudBlobContainer.GetBlockBlobReference($blobName)

#download the blob as text into memory
$download_file = $source_blob_client.DownloadText()

$jsonContent = $download_file | ConvertFrom-Json

#Loop through json content and manipulate it
For ($i=0; $i -lt $jsonContent.Length; $i++) {
    $jsonContent[$i] | Add-Member -NotePropertyName "id" -NotePropertyValue $jsonContent[$i]._id
    $jsonContent[$i].PsObject.Properties.Remove("_id")
    $jsonContent[$i].PsObject.Properties.Remove("_rev")
}

$replaced_json = $jsonContent | ConvertTo-Json

#upload the json file
$dest_blob_client =  $container_client.CloudBlobContainer.GetBlockBlobReference($outputBlobName)
$dest_blob_client.Properties.ContentType = "application/json"
$dest_blob_client.UploadText($replaced_json)

Write-Output("**completed**")