_1 suffix column while importing JSON file?

Question & Answer
, , ,
1

I am trying to import a JSON file from S3 and followed the steps mentioned in the documentation. The import is successful and I can also view the resulting dataset in quicksight.

However, for some of the numeric columns let’s say price a new column with the name price_1 is also present in the source table/dataset. The values in price_1 is the same as price column. Why is this additional column being added by automatically by quicksight? The price_1 column is not present in the S3 JSON file and no calculated field has been added. I could not find any documentation around this behavior.

While this does not seem like a big problem at first, but it causes the dataset refresh to fail if I update the JSON file in S3 and quicksight decides not to add the price_1 column anymore. The refresh fails due to mismatch in number of columns.

Is there something that can be done here to prevent this additional column from coming in? Curiously the issue occurs only for numeric columns.

2

I’m assuming you have two different price keys.

Here would be an example.

{‘price’: ‘{‘body’:’{‘price’:10}}}

If you want to move away from this I would suggest using Athena as you can run SQL on JSON in S3 and exclude / reformat fields.

1 Like
3

@k_anubhav You can also exclude column from QuickSight edit dataset page.

Did @Max solution help? If so, please help the community out by marking this answer as “Solution!”

1 Like
4

I reached out to AWS support and they mentioned that this is a known issue which occurs if a numeric column contains both integer and decimal values. The work around is to write all values as decimal(use 20.00 instead of 20) if there are mixed types in a column or enclose all the values in quotes (“20”) to make it a string type and then convert the type in edit dataset page.

1 Like
5

Thanks @k_anubhav for sharing this workaround with the QuickSight Community!