Suggested approaches for manipulating JSON values

Henry Simms · December 2024

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?

Li He · December 2024

Hi Henry,

Some new JSON transformations were introduced in 2.15.8+ that will help with your requirements.

{74686CC9-75F1-41F1-9629-EB7270CF6288}.png

Extracting JSON:

Extract from record:
You can specify the key, in this case 'red' (case-sensitive), to retrieve the RGB value.

{C6447402-68B3-4094-A413-91F33A1FEC11}.png

{08CCD99B-293F-4E77-AE1F-911123253EA4}.png

For more complex/ nested JSON, you can chain multiple 'Extract from record' to dive deeper e.g.:

{F5F63280-C3F2-4905-AA9B-5C020F79279C}.png

{69D430CA-D88D-4B33-B1B1-6DAC3EBB6645}.png

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

{6ABB84D1-9BB8-433E-B99E-21F1F458A183}.png

Building JSON:

For building new JSON objects, you can use 'To record' by specifying the key-values pairs.

{28AA39FC-148F-4E3D-BD04-AB869E1AD5C3}.png

{62DA0665-0659-4B06-8136-86A113E0D6D2}.png

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.

{1F4B8119-5AB6-4BAF-8675-D9E17573C357}.png

{EE72DC9C-8A71-448E-A468-F127D7BD85B2}.png

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:

{6CF82936-C0C6-4CE7-919C-B8D40A83936A}.png

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 :)

Henry Simms · September 30

I was recently asked a question around creating JSON objects:

From our data, we are trying to generate a json file like this one:

{"key1": "value1", "key2": [{"key21": "value21"}, {"key22": "value22"}], "key3": "value3"}

We are having an issue with the "key2", which it's value is a list of json, and it's being difficult that a workflow step handles this type appropriately.

The approach for creating the "key2" (array containing two JSON objects) is to use the Create list function to create a list of the "key21" and "key22" records.

In my example below, I have four columns:

Postcode
Lat
Long
Match Level

And I want to create an equivalent JSON structure to the one in the question:

{"postcode":"M90 3HG","coords":[{"lat":"53.361695"},{"long":"-2.271935"}],"result":"Tentative Full Match"}

In my example "coords" is the "key2", and its value is an array of two JSON objects. This is created as follows:

Note: In my example I've kept all data types as string for simplicity. In reality I'd likely want to use To number on the lat and long values before creating those records.

Suggested approaches for manipulating JSON values

Best Answer

Answers

Categories