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

Question

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:

Replace all "_id" with "id"
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"
    ]
  }
]

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
– Gaurav Mantri, Commented Mar 13, 2021 at 2:39
What version of SDK you're using. Also this is C# code. You mentioned PowerShell. — Gaurav Mantri
– Gaurav Mantri, Commented Mar 13, 2021 at 3:13
SDK 3.0. I was trying C#. But ask was PowerShell if possible. no idea its doable using PowerShell — Dinesh Madhup
– Dinesh Madhup, Commented Mar 13, 2021 at 4:26
I removed C# code which I tried. Lets not confuse other as ask was using PowerShell if possible — Dinesh Madhup
– Dinesh Madhup, Commented Mar 13, 2021 at 4:29

Ivan Glasenberg · Accepted Answer · 2021-03-16 09:21:31Z

4

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**")

edited Mar 16, 2021 at 9:21

answered Mar 16, 2021 at 8:44

Ivan Glasenberg

30.3k2 gold badges56 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ShresthaGanesh Over a year ago

Not to mention but do not forget to pass the encoding type if the content in the file follows any specific encoding format. I have a file in unicode format and the simple DownloadText() would return me the text with extra spaces in each of the character.

Gaurav Mantri · Accepted Answer · 2021-03-13 11:11:42Z

1

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

answered Mar 13, 2021 at 11:11

Gaurav Mantri

137k17 gold badges247 silver badges282 bronze badges

1 Comment

Dinesh Madhup Over a year ago

Thanks Gaurav. Not sure whether its possible to store file in memory rather than local disk

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related