@Tony.Zhu_R

Please try this query:

jq-win64.exe ".value |= sort_by(.attributes | from_entries | .size | tonumber)"

To reverse the order, please use this query.

jq-win64.exe ".value |= sort_by(.attributes | from_entries | .size | tonumber) | .value |= reverse"

Hello @Tony.Zhu_R ,

I think you are requesting Filsets:

https://{
                {RDP_HOST}}/file-store/{
                {RDP_VERSION}}/file-sets?bucket={
                {RDP_TH_Bucket}}&attributes=venue:{
                {CFS_VBD_VENUE}},view:normalised

Which makes sorting on file size challenging?

Try requesting File details by Fileset id:

https://{
                {RDP_HOST}}/file-store/{
                {RDP_VERSION}}/files?filesetId={
                {fileSetId}}

The output should look like:

{
    "value": [
        {
            "id": "4709-44ae-ae48bed4-8651-6ca30681bec8",
            "filename": "LSE-2018-06-01-NORMALIZEDMP-Report-1-of-1.csv.gz",
            "filesetId": "4000-ee57-9e402927-8e3e-a967e20d26f9",
            "fileType": "File",
            "description": "Merged data file",
            "storageLocation": {
                "url": "https://s3-us-east-1.amazonaws.com/a205143-use1-prod-results-vbd/normalised/LSE/2018-06-01/data/merged/LSE-2018-06-01-NORMALIZEDMP-Report-1-of-1.csv.gz",
                "rolearn": "arn:aws:iam::259431915815:role/a205143-prod-push-mechanism-EdsCfsS3Access",
                "@type": "s3"
            },
            "created": "2022-06-25T02:33:09Z",
            "modified": "2022-06-25T02:33:09Z",
            "href": "https://api.refinitiv.com/file-store/v1/files/4709-44ae-ae48bed4-8651-6ca30681bec8/stream",
            "fileSizeInBytes": 904974,
            "md5": "1d9d418dd96e7659c055b857c2bfc0a9"
        },
        {
            "id": "497f-2e71-5796a24e-8162-917fef100c75",
            "filename": "LSE-2018-06-01-NORMALIZEDMP-Data-1-of-1.csv.gz",
            "filesetId": "4000-ee57-9e402927-8e3e-a967e20d26f9",
            "fileType": "File",
            "description": "Merged data file",
            "storageLocation": {
                "url": "https://s3-us-east-1.amazonaws.com/a205143-use1-prod-results-vbd/normalised/LSE/2018-06-01/data/merged/LSE-2018-06-01-NORMALIZEDMP-Data-1-of-1.csv.gz",
                "rolearn": "arn:aws:iam::259431915815:role/a205143-prod-push-mechanism-EdsCfsS3Access",
                "@type": "s3"
            },
            "created": "2022-06-25T02:33:09Z",
            "modified": "2022-06-25T02:33:09Z",
            "href": "https://api.refinitiv.com/file-store/v1/files/497f-2e71-5796a24e-8162-917fef100c75/stream",
            "fileSizeInBytes": 1067179413,
            "md5": "591c5c30619f26e046cfc8ea1857fa5a"
        }
    ]
}

Enabling you to sort on fileSizeInBytes.

Will this work for your use case, or you are looking for something different?

@zoya faberov

The JSON I put in above is got from

https://api.refinitiv.com/file-store/v1/file-sets?bucket=TICKHISTORY_CUSTOM_ARCHIVE

Normally, it responds with 25 filesetIds but up to 100 when pageSize = 100 specified.

What I asked is how to use jq to sort those filesetIDs by its attribute of size.

{
          "name": "size",
          "value": "2850978035"
        },

For instance, I need to filter out those filesetIDs with sizes of more than 100GBytes what's the filter to use for jq if the total number of filesetIDs is hundreds?

Hello @Tony.Zhu_R ,

Let me try to explain what I meant, hoping that the explanation will help clarify. I believe that the size that you obtain from the call is not the size of a file, rather is the size of a fileset. For example:

https://{
                {RDP_HOST}}/file-store/{
                {RDP_VERSION}}/file-sets?bucket={
                {RDP_TH_Bucket}}&attributes=venue:{
                {CFS_VBD_VENUE}},view:normalised

Results in:

{
    "value": [
        {
            "id": "4000-0fef-8c14bb0e-ac4a-9d9356699d2f",
            "name": "LSE_normalised_2007-05-28",
            "bucketName": "TICKHISTORY_VBD_UNLIMITED",
            "packageId": "40eb-1240-f887f89f-b6e2-ff08ec2e2603",
            "attributes": [
                {
                    "name": "message_id",
                    "value": "f546fa27-7d1f-477a-89a6-ba2f3ca325e2"
                },
                {
                    "name": "view",
                    "value": "normalised"
                },
                {
                    "name": "depth",
                    "value": "UNLIMITED"
                },
                {
                    "name": "size",
                    "value": "23001"
                },
                {
                    "name": "releaseTime",
                    "value": "2007-05-29T04:00:00.000000000Z"
                },
                {
                    "name": "venue",
                    "value": "LSE"
                },
                {
                    "name": "md5",
                    "value": "565390e25c255a712e3d42ef790e07f0"
                }
            ],
            "files": [
                "4414-fdfd-35d0e262-be9a-664b4ea09787",
                "44d8-267c-5a5af2c9-adbe-9abe81c1c060"
            ],
            "numFiles": 2,

Whereas:

https://{
                {RDP_HOST}}/file-store/{
                {RDP_VERSION}}/files?filesetId=4000-0fef-8c14bb0e-ac4a-9d9356699d2f

Results in:

"value": [
        {
            "id": "4414-fdfd-35d0e262-be9a-664b4ea09787",
            "filename": "LSE-2007-05-28-NORMALIZEDMP-Report-1-of-1.csv.gz",
            "filesetId": "4000-0fef-8c14bb0e-ac4a-9d9356699d2f",
            "fileType": "File",
            "description": "Merged data file",
            "storageLocation": {
                "url": "https://s3-us-east-1.amazonaws.com/a205143-use1-prod-results-vbd/normalised/LSE/2007-05-28/data/merged/LSE-2007-05-28-NORMALIZEDMP-Report-1-of-1.csv.gz",
                "rolearn": "arn:aws:iam::259431915815:role/a205143-prod-push-mechanism-EdsCfsS3Access",
                "@type": "s3"
            },
            "created": "2021-04-22T15:33:18Z",
            "modified": "2021-04-22T15:33:18Z",
            "href": "https://api.refinitiv.com/file-store/v1/files/4414-fdfd-35d0e262-be9a-664b4ea09787/stream",
            "fileSizeInBytes": 22560,
            "md5": "fda504b3a6efa56a34db998e2128b7f0"
        },
        {
            "id": "44d8-267c-5a5af2c9-adbe-9abe81c1c060",
            "filename": "LSE-2007-05-28-NORMALIZEDMP-Data-1-of-1.csv.gz",
            "filesetId": "4000-0fef-8c14bb0e-ac4a-9d9356699d2f",
            "fileType": "File",
            "description": "Merged data file",
            "storageLocation": {
                "url": "https://s3-us-east-1.amazonaws.com/a205143-use1-prod-results-vbd/normalised/LSE/2007-05-28/data/merged/LSE-2007-05-28-NORMALIZEDMP-Data-1-of-1.csv.gz",
                "rolearn": "arn:aws:iam::259431915815:role/a205143-prod-push-mechanism-EdsCfsS3Access",
                "@type": "s3"
            },
            "created": "2021-04-22T15:33:18Z",
            "modified": "2021-04-22T15:33:18Z",
            "href": "https://api.refinitiv.com/file-store/v1/files/44d8-267c-5a5af2c9-adbe-9abe81c1c060/stream",
            "fileSizeInBytes": 441,
            "md5": "4da4c501efd0f042bb7d1c1b5130306c"
        }
    ]

23001 = 22560+441

Before starting to parse this, are you looking for sizes of files, or sizes of FileSets, that can potentially contain multiple files?

If you are looking for sizes of FileSets, you can paginate over your results, concatenate them into a dataframe and derive fileset sizes.

If you are looking for sizes of files, you will need an extra step- to paginate through FileSets results from the bucket, and request either File details by FilesetID or File Details by FileID. Both will contain the sizes of the discrete files. You could concatenate these results into a dataframe and obtain all file sizes from this dataframe.

At this time, I am not seeing any files in TICKHISTORY_CUSTOM_ARCHIVE, but this is likely because I lack the permissions to access this bucket.

Does this explanation make sense, is this what you observe on your side?

Hi @Tony.Zhu_R ,

I would like to mention another approach that you may find of interest- RD Library Python.

Please see example:

https://github.com/Refinitiv-API-Samples/Example.DataLibrary.Python/blob/main/Examples/3-Delivery/3.03-Bulk/EX-3.03.02-Bulk-ClientFileStore-TickHistory.ipynb

Section: Browse Tick History buckets.

This I believe will fulfill your requirement, except for pagination.

Additionally see example:

https://github.com/Refinitiv-API-Samples/Example.DataLibrary.Python/blob/main/Examples/3-Delivery/3.03-Bulk/EX-3.03.01-Bulk-ClientFileStore.ipynb

Section: Get FileSets with pagination

Thanks for above sharing, @zoya faberov

It's not what I asked for, though.

Endpoint: https://api.refinitiv.com/file-store/v1/file-sets?bucket=TICKHISTORY_CUSTOM_ARCHIVE&pageSize=3&attributes=view%3Anormalised

Given the number of fileset in RTH HMC bucket varies, I take 3 as an example with following response.

{
  "value": [
    {
      "id": "4030-fd1e-db11637d-b277-19bbd95c73ac",
      "name": "salesforce-1-XXX_normalised_3C4C0EC0D2A643188BB5E9C1F8C329C0",
      "bucketName": "TICKHISTORY_CUSTOM_ARCHIVE",
      "packageId": "4a13-95e9-c37783d6-bba0-4851594758d0",
      "attributes": [
        {
          "name": "size",
          "value": "3634152545"
        },
        {
          "name": "md5",
          "value": "612d8ee7b77605bc342f148c16db2929"
        },
        {
          "name": "message_id",
          "value": "a5c136ba-4821-4898-a511-18bfa607c86f"
        },
        {
          "name": "releaseTime",
          "value": "2022-08-17T10:45:56Z"
        },
        {
          "name": "fileName",
          "value": "hmh tas 2015"
        },
        {
          "name": "view",
          "value": "normalised"
        }
      ],
      "files": [
        "4745-b059-944935e3-a2c1-1f47875d39a1",
        "4b21-037a-58a832d2-b486-ff831a2e223e",
        "4bb1-dc27-81b6d485-8cbc-9854df8c651b"
      ],
      "numFiles": 3,
      "availableFrom": "2022-08-17T12:07:46Z",
      "availableTo": "2023-01-18T08:13:10Z",
      "status": "READY",
      "created": "2022-08-17T12:07:46Z",
      "modified": "2022-08-17T12:07:47Z"
    },
    {
      "id": "4039-98c8-91ff43cc-95c8-646c7f51f25c",
      "name": "salesforce-1-XXX_normalised_1D80CBA8BE694370B21F08080015732C",
      "bucketName": "TICKHISTORY_CUSTOM_ARCHIVE",
      "packageId": "4a13-95e9-c37783d6-bba0-4851594758d0",
      "attributes": [
        {
          "name": "message_id",
          "value": "94344e73-7d97-4c8a-a773-5246edfeaac3"
        },
        {
          "name": "md5",
          "value": "51c1b3242c42307a6b51383e7d9e2f65"
        },
        {
          "name": "releaseTime",
          "value": "2022-08-17T10:45:27Z"
        },
        {
          "name": "fileName",
          "value": "hsi tas 2017"
        },
        {
          "name": "view",
          "value": "normalised"
        },
        {
          "name": "size",
          "value": "11812238421"
        }
      ],
      "files": [
        "42b7-420b-b2e9cb81-9340-15f12d2e1b52",
        "43d9-8883-8c8649e2-a96c-eaab9b70bed3",
        "4e8e-c1a1-60ce42ad-bcf2-9cfa55b8578a"
      ],
      "numFiles": 3,
      "availableFrom": "2022-08-17T12:05:46Z",
      "availableTo": "2023-01-13T17:37:03Z",
      "status": "READY",
      "created": "2022-08-17T12:05:46Z",
      "modified": "2022-08-17T12:05:47Z"
    },
    {
      "id": "4061-5f65-2a3a0753-aab7-9f2abaec0016",
      "name": "salesforce-1-XXX_normalised_88D37A2541B44CEEBE715A1300C4F175",
      "bucketName": "TICKHISTORY_CUSTOM_ARCHIVE",
      "packageId": "4a13-95e9-c37783d6-bba0-4851594758d0",
      "attributes": [
        {
          "name": "releaseTime",
          "value": "2022-08-17T10:45:56Z"
        },
        {
          "name": "message_id",
          "value": "b94f669b-a925-4cca-ba7c-911d2811223a"
        },
        {
          "name": "size",
          "value": "17227956"
        },
        {
          "name": "view",
          "value": "normalised"
        },
        {
          "name": "md5",
          "value": "c8db431e72af8ae0599bf9eead6a2a48"
        },
        {
          "name": "fileName",
          "value": "hmce tas 2005"
        }
      ],
      "files": [
        "4842-db68-deb53105-ad1b-48c4126d38d4",
        "4cd5-e0e2-543e2fd3-b8c8-0738f180eb24",
        "4ed5-1054-3b985648-8301-682f0c61e3ba"
      ],
      "numFiles": 3,
      "availableFrom": "2022-08-17T12:08:20Z",
      "availableTo": "2023-01-18T08:33:00Z",
      "status": "READY",
      "created": "2022-08-17T12:08:21Z",
      "modified": "2022-08-17T12:08:21Z"
    }
  ]
}

It's nothing to do with exact file size irrespective of fileset or its constituent files. The question is what kind of filter to use with 'jq' to sort the above response by its attribute below.

 {
          "name": "size",
          "value": "17227956"
        },

For instance, I like to see the all filesets in order of its size, largest to smallest, and vice versa.

So, any idea what kind of 'jq' filter can achieve it?

Hi @Tony.Zhu_R ,

A quick disclaimer- I am not permissioned for the same content in custom archive bucket. I am testing with TICKHISTORY_VBD_UNLIMITED, which I believe to be structured the same as custom. But if you see any discrepancies in the format on your side- please let me know.

What I would like to discuss is not the only approach to sorting this result, but possibly the simplest: putting the result into a dataframe. The added benefit can be that if you would require additional transformations, next, you can do them with a dataframe making them simpler as well.

df = pd.json_normalize(jsonFullResp['value'])

Next, I create a new column size, that contains the size value, and convert the value to integer, so that we can sort the column as containing integer values, not as strings.

for index, row in df.iterrows():
    print(index, [sub['value'] for sub in df['attributes'][index] if sub['name'] == 'size'])
    df.loc[index,'size'] = [sub['value'] for sub in df['attributes'][index] if sub['name'] == 'size']
    df.loc[index,'size'] = int(df.loc[index,'size'])

My result looks thus:

And now I can sort it:

df2 = df.sort_values(by=['size'])
df2

Getting:

Please let us know if this is what you are looking to do?

Hi @Tony.Zhu_R ,

I cannot test with your exact result at the moment, but I would expect the larger result sets not to be made available in a single request, and to require iterative pagination via skip token.

If that is the case, we can concatenate each new page of the results to the total results dataframe, and sort by integer size only once the result that is obtained is complete.

Let us know if this is something that you observe to be required, and would like to discuss more?

@zoya faberov

As raised initially, I'm looking for 'filter' to run with jq so sort the fileset by its 'size'

Can you use above sample of 3 filesets and sort them by size of each with jq instead of pandas?