API CLI Question - table view of data set ids with multiple owners?

I am trying to output a table using CLI that has two columns, with column A showing the data set ID and column B including all principals associated with that data set. I need this to know who to notify when working on cleaning up un-used datasets to clear up SPICE capacity.
Using the following CLI script, however, results in two different table views, depending on the number of principals per data set. When there’s only one principal for a data set, the table format is acceptable. However when there are multiple principals, the table is not correct.

aws quicksight describe-data-set-permissions --aws-account-id 000000000000 --data-set-id 00000000-0000-0000-0000-000000000000 --query '{DataSetId:DataSetId,Principal:to_string(Permissions|[].Principal)}' --output table

Result with one principal per dataset:

Result with multiple principals per dataset:

Is there a way to output a table with the desired two columns when a dataset has multiple principals?

Hi @Ben_Berliner ,

You can try --query '{DataSetId:DataSetId,Principal:Permissions[].[Principal]}' for your query parameter. This should give you the multiple principals in different rows.

Did this answer your question? If so, please help the community out by marking this answer as “Solution!”

Hi @salim , thanks for your suggestion. Unfortunately, the table output looks as follows:

This is not in the format I need, with just two columns, where column A is ‘DataSetId’ and Column B is ‘Principal’. Please let me know if there is another way to structure the query to make it output in a table formatted that way. Thanks!

Hi @Ben_Berliner ,

Unfortunately I am not aware of a way you can get that output as described. The only way I can think of would be to use the --query as suggested but change --output to json. Then process the JSON separately in to the format you’re after.

Hope that helps.