powershell - How do I get the following value out of this JSON response?

Question

Basically, I have a retrieved values from a DynamoDB using Powershell and have gotten a row in JSON format like the following

      function gettagsfromdynamo() {
        $table_name = "table_name"
        $dbkey = '{"ReleaseId":{"AttributeValueList":[ {"N":"1"} ],"ComparisonOperator": "EQ"}}' | ConvertTo-Json -Compress
        $dbvalue = aws dynamodb query --table-name $table_name --key-conditions $dbkey --region $region
        $latest_tags = $dbvalue
        $latest_tags
      }
     
      $db_tags = gettagsfromdynamo

This is what db_tags looks like

{
    "Items": [
        {
            "Comment": {
                "S": "The first PC release."
            },
            "Product": {
                "S": "PC"
            },
            "ReleaseId": {
                "N": "1"
            },
            "CreatedOn": {
                "S": "12/14/2020 15:23:32"
            },
            "Tags": {
                "S": "{
    "software1": "software1.zip",
    "software2": "software2.zip",
    "software3":
    [
        "software3.zip",
        "software4.zip",
        "software5.zip"
    ],
    " data1 ": "2020_NA",
    " 2020_EU ": "20201_EU",
    " 2020_WW ": "2021_WW",
    " dataversions":
    [
        "2020",
        "2019",
        "2018",
        "2017"
    ],
    " products ": " "
}"
            }
        }
    ],
    "Count": 1,
    "ScannedCount": 1,
    "ConsumedCapacity": null
}

The task I want to achieve is to be able to get the "dataversions" value which is --> [Items][Tags][Dataversions] and write that value to a JSON file available locally. I have tried various things including using the Convert-ToJson and ConvertFrom-Json.

The tags json value looks like this without the escaped spaces (/n)

{
    "software1": "software1.zip",
    "software2": "software2.zip",
    "software3":
    [
        "software3.zip",
        "software4.zip",
        "software5.zip"
    ],
    " data1": "2020_NA",
    " 2020_eu ": "2020_EU",
    " 2020_ww": "2021_WW",
    " dataversions":
    [
        "2020",
        "2019",
        "2018",
        "2017"
    ],
    " products ": " "
}

How do I retrieve the value of 'dataversions', which is a list of strings. Right now, I can only get it like this after using various tries:

{"S":"{
    "software1": "software1.zip",
    "software2": "software2.zip",
    "software3":
    [
        "software3.zip",
        "software4.zip",
        "software5.zip"
    ],
    " data1": "2020_NA",
    " 2020_EU": "20201_EU",
    " 2020_WW": "2021_WW",
    " dataversions":
    [
        "2020",
        "2019",
        "2018",
        "2017"
    ],
    " products": " "
}"}

I want to be able to get the value of dataversions inorder to overwrite another 'dataversions' which is inside example.json file. How do I get to the dataversions value and also clean up the ?

Answer 1

In your JSON file, the property name " dataversions" contains a leading space. When the JSON string is converted to a custom object (via ConvertFrom-Json), the space will be included in the property name. Therefore it must be considered when using member access (object.property) syntax:

($db_tags | ConvertFrom-Json).' dataversions'