Suggested approaches for manipulating JSON values
My data includes a column containing JSON values - I've made a dummy example below:
Example JSON value:
{
"magenta": "#f0f",
"yellow": "#ff0",
"black": "#000",
"usageList": [
"giraffe",
"lion",
"axolotl"
],
"properties": {
"cost": 99,
"difficulty": {
"part_a": "hard",
"part_b": "easy"
}
}
}
I have a couple of requirements:
- To parse out certain values from the JSON string based on given keys
- To construct new JSON objects dynamically from column values
What's the best way to do this in Data Studio?
Best Answer
-
Hi Henry,
Some new JSON transformations were introduced in 2.15.8+ that will help with your requirements.
Extracting JSON:
Extract from record:
You can specify the key, in this case 'red' (case-sensitive), to retrieve the RGB value.
For more complex/ nested JSON, you can chain multiple 'Extract from record' to dive deeper e.g.:
Alternatively, you can use 'JSON path' to traverse nested JSON with a single transform and achieve the same result.
'$' - to begin the expression
'.' - to identify the key
'\[0\]' - for arrays, the '[ ]' have to be escaped.
Building JSON:
For building new JSON objects, you can use 'To record' by specifying the key-values pairs.
With all these new steps, you can begin build a series of transforms to extract and rebuild the JSON to your needs, nested JSON included.
Dynamically building JSON depending on what values are present/ empty will be tricker because you will need to utilise workflows, automations, fire event and breaking the JSON down to indivdual components. (I will update this thread if I can come up with something of use).
At this moment, I can recommend using 'Regular expression replace' to parse/ remove/ edit certain patterns in your JSON.
e.g. removing 'red' key-values in the example JSON:
RegEx: "red":\s*"[a-zA-Z0-9#]*",?
Replacement value: 'Null' (literal value)
One thing to note when using JSON path, the results are returned as a list (even for single values) and you can use the 'To alphanumeric'/ 'Extract from list' to remove the array brackets.
Hope the above helps :)4
