parseJson() function referencing a JSON key with space

Question & Answer
, ,
1

Hi,
I’m trying to use the QuickSight parseJson function to add a calculated field to my dataset, in order to reference a JSON which has a property name with a space, like:

{ "my key": "a value" }

I’ve tried:

parseJson({aJson}, '$."my key"')
and
parseJson({aJson}, '$["my key"]')

but it doesn’t work.

Is it supported?
Many thanks

2

Hi Antonio,
Is your dataset a SPICE or direct query mode? There are some restrictions on using ParseJson function in calculated fields depending on your dataset mode. Refer doc here. Not sure whether your example above is simple JSON or nested and whether it is directly under root or under a child attribute. The space in the value is not an issue, I would recommend you to follow the syntax in ParseJSON reference depending on whether the value you are looking for is directly under root or child node.
Thanks

3

Hi @Raji_Sivasubramaniam,
my dataset is SPICE, with custom query based on an Athena datasource. The calculated field I’m trying to create is added during the data preparation at dataset level (as documented for SPICE). JSON data is stored as string in a column and it is very simple, the property name with a space is at the JSON root level (not nested) and I’m able to create a calculated field accessing the same JSON for a key without spaces in the name, using the parseJson() function as: parseJson({ajson}, '$.k1'), for example.
Trying to add a calculated field pointing to the JSON property with a space in the name, like parseJson({ajson}, '$["my key"]') it doesn’t work.

4

If that’s not working I would suggest doing it in Athena.

https://prestodb.io/docs/current/functions/json.html

You can do a lot more with Athena’s built in functions.

1 Like
5

Hi @Max ,
yes, in the query, using the Athena json_extract_scalar() function it works as expected, but I’m trying to understand if it is a bug/limitation of the QuickSight parseJson() function or if I’m using a wrong syntax.
Thank you.

6

I will mark it as a bug for now. I couldn’t get it to work. Maybe someone else could figure it out but I couldn’t.

1 Like