Using parseJson on JSON field in dataset to seperate an ID

Question & Answer
, ,
1

Hello,

As part of the Data Prep, in the dataset we have a JSON field which also contains an Unique ID. this ID we are trying to seperate it out to a calculated field for easier analysis later.

I am using: parseJson({field},‘$.Data.0.uniqueId’) to separate column.

{
“Data”: [
{
“uniqueId”: “AAAAAA”,
“groups”: [
{
“subType”: “stores”,
“rotationId”: “AAAA12121212”
},
{
“subType”: “inventory”,
“rotationId”: “BBBB12121212”
}
]
}
]
}

On Saving the column is seen in analysis. However on saving and publish when I open in Analysis, it says failed to load data… Spice pull fails.

Is there any additional things need to be done. Please let know.

Thanks.

2

@Maheshj,
If your dataset is stored in SPICE, you can use parseJson when you are preparing a data set, but not in calculated fields during analysis.

For direct query, you can use parseJson both during data preparation and analysis. The parseJson function applies to either strings or to JSON native data types, depending on the dialect

So in case of SPICE dataset, apply parsejson in dataset prep editor only.

Let me know if this help to address your issue.

Thank you
Deepak

1 Like
3

Hello @DeepakS,

Thanks for the response.

Dataset is stored in Spice. I am trying to add the calculated field during data prep for the Dataset.

This issue is happening during Dataset preparation.

Please advise what could be the issue.

Thanks,
Mahesh

4

@Maheshj, thanks for confirming that you are facing this issue during data prep and this is a SPICE dataset.
Are you able to preview the unique id column value in data prep editor and then save and publish the dataset? If yes, use this unique id column to create a simple table visual only with this column and let me know if that works for you or you receive an error.

Thank you
Deepak

5

Hello Deepak,

Yes the calculated field captures the value and I am able to see the value in the field.
Save and publish also happens… then when the analysis opens, there the spice data load fails…

Regards.

6

Hi Mahesh,

Since you can preview the parsed json data column in data prep editor but unable to use it in analysis, please go ahead and open a support ticket.

Thanks
Deepak

1 Like