Parsing json and assoicating it to the row

Question & Answer
,
1

Hey all!

I have multiple columns with strings and one column with single level json string. Would it be possible to have

{“example-metrics-service-base”:“7.0.0”,“example-service-rollout-hpa-base”:“7.0.0”,“example-service-rollout-spring-mvc-base”:“5.6.2”,“example-service-rollout-ingress-base”:“7.0.0”}

I was wondering what the best way would be to associate each key and value with the remaining columns of the row. Would it be possible to dynamically create calculated fields based on the keys?

2

Hi @quicksight-user001 and welcome to the QuickSight community!

Take a look at the attached article below and let me know if this helps with your case.
Let us know if you have any additional questions!

1 Like
3

Thanks @Brett. I’ve tried using this function during data prep and analysis but it doesn’t work for me. The column is empty during data prep and I get Your calculated field expression contains invalid syntax during analysis

4

HI @quicksight-user001
can you show the syntax?
BR

5

Sure @ErikG
parseJson(basename, "$.example-metrics-service-base")

6

Hi @quicksight-user001 - Looks like the issue is happening due to “-” in the column name. Is it possible to replace by underscore (“_”) and test it.

Hi @DylanM - Looks like parseJson is not working if the column name contains _. can you please check whether we have any work around for the same or raise it as a feature request.

Regards - Sanjeeb

2 Likes
7

Nice catch @Sanjeeb2022, that fixed it for me. Thanks a lot!

My keys can vary between rows, so I cannot create calculated fields for them.
Is there any other way to associate the key-values to the rest of the row?

8

hi @quicksight-user001 - Thanks… For your second use case, can you please give some sample example like your input and desired output.

Regards - Sanjeeb

9

Sure @Sanjeeb2022. One of my rows look like this. I’m trying to get each key in the base_data column to be a new field that is associated with the rest of the row. Keys can differ between rows.

To put it simply, I want to be able to chart what bases(base_data keys) and its version(base_data value) an asset_id is using.

asset_id base_data github_url timestamp
12345635676523 {“example_metrics_service_base”:“7.0.0”,“example_service_rollout_hpa_base”:“7.0.0”,“example_service_rollout_spring_mvc_base”:“5.6.2”,“example_service_rollout_ingress_base”:“7.0.0”} https://github.com/myOrg/myRepo 2024-12-09T18:30:00Z
10

Ended up using custom sql during data prep to do this.

1 Like