Unable to query Azure Table Storage using Azure CLI-CodePudding

I wanted to filter the entry in my Azure Storage Table and the structure looks like the following. I wanted to filter the entry's based on the given Id for example JD.98755. How can we achieve this?

{
  "items": [
    {
      "selectionId": {
        "Id": "JD.98755",
        "status": 0
       },
    "Consortium": "xxxxxx",
    "CreatedTime": "2019-09-06T09:34:07.551260 00:00",
    "RowKey": "yyyyyy",
    "PartitionKey": "zzzzzz-zzzzz-zz-zzzzz-zz",
    "Timestamp": "2019-09-06T09:41:34.660306 00:00",
    "etag": "W/\"datetime'2019-09-06T09:41:34.6603060Z'\""
    }
  ],
  "nextMarker": {}
}

I can filter other elements like the Consortium using the below query but not the Id

az storage entity query -t test --account-name zuhdefault --filter "Consortium eq 'test'"

I tried something like the following to filter based on the given ID but it has not returned any results.

az storage entity query -t test --account-name zuhdefault --filter "Id eq 'JD.98755'"
{
  "items": [],
  "nextMarker": {}
}

CodePudding user response：

The reason you are not getting any data back is because Azure Table Storage is a simple key/value pair store and you are storing a JSON there (in all likelihood, the SDK serialized JSON data and stored it as string in Table Storage).

Considering there is no key named Id, you will not be able to search for that.

If you need to store JSON document, one option is to make use of Cosmos DB (with SQL API) instead of Table Storage. Other option would be to flatten your JSON so that you store them as key/value pair. In this scenario, your data would look something like:

{
  "selectionId_Id": "JD.98755",
  "selectionId_status": 0,
  "Consortium": "xxxxxx",
  "CreatedTime": "2019-09-06T09:34:07.551260 00:00",
  "RowKey": "yyyyyy",
  "PartitionKey": "zzzzzz-zzzzz-zz-zzzzz-zz",
  "Timestamp": "2019-09-06T09:41:34.660306 00:00",
  "etag": "W/\"datetime'2019-09-06T09:41:34.6603060Z'\""
}

then you should be able to filter by selectionId_Id.

CodePudding user response：

I do agree with @Gaurav Mantri and I guess one of other approach you can use is:

I have reproduced in my environment and got expected results as below:

Firstly, you need to store the output of the command into a variable like below:

I have stored output in $x variable:

$x

enter image description here

Then you can change the output from Json:

$r= $x | ConvertFrom-Json

enter image description here

Then you can store items.id value in a variable like below:

enter image description here

Now you can use below command to get the items with Id JD.98755:

enter image description here

If you have more data, then store the first output into variable then divide them into objects using ConvertFrom-json and then you use the above steps from first.