parseJSON returns only the first json key value from an array

Question & Answer
, ,
1

Hi Everyone,
I am new to quicksight and i’m trying to prepare a dataset for analyses. I wonder could anyone help me I Have a field that has the json data look like this
[
{
‘key1’:‘abc’,
‘key2’:‘def’,
‘key3’:‘ghi’,
‘key4’:‘jkl’,
‘key5’:‘mno’

},
{
‘key1’:‘pqr’,
‘key2’:‘stu’,
‘key3’:‘vwx’,
‘key4’:‘yza’,
‘key5’:‘bcde’

}
]
I am trying to use the QuickSight Data Prep tool to create a calculated field to extract data from this field. I want to extract values from key 1 in my calculated field like abc, pqr. I have tried it using parseJson({column}, “$.0.key1”) and it returns only the 1st array value i.e.,abc . Is it possible to parse json data with multiple values using parseJson func in quicksight? Let me know the ways to handle this. Any suggestions would be greatly appreciated!

Thank You!

2

Hi @VarshaSaravanan - Welcome to AWS QuickSight community and thanks for posting the question. Since the JSON is the part of the field and it may happen you have multiple values for each row may have multiple json value. I believe you need to develop a custom code and split data out of QuickSight as each row need to split into multiple rows. I believe you may not able achieve this in QuickSight.

By saying that tagging @Ashok @Max @David_Wong @Biswajit_1993 for the expert advise.

Regards - Sanjeeb

1 Like
3

@VarshaSaravanan

It is not possible to grab multiple values from an array.

I would suggest looking into Athena and the unnest function.

Athena is based off of presto db.

https://prestodb.io/docs/current/sql/select.html#unnest

1 Like
4

Thanks for your answer @Sanjeeb2022

1 Like
5

Thanks for Your suggestion @Max

1 Like
6

Hi @VarshaSaravanan - If any of the above post help you in guiding the solution, please marked as solution so that it will help to the wider community.

Have a great week ahead.

Regards - Sanjeeb