Official Documentation: https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/buildrowsetfromjson.html

BuildRowsetFromJSON() is a new function that was added to AMPscript in the Summer 2023 release for Salesforce Marketing Cloud. It provides the ability for AMPscript to interact and iterate through a JSON Object or Array in a more native fashion. This is compared to previously when you would have to utilize a different platform language or utilize regex and substring, etc. (which was far from accurate, elegant or efficient).

If there were other languages in SFMC that could parse and iterate through a JSON, why would we need this?

Why is it a big deal?

Well, AMPscript is by far the most performant language inside of a messaging environment and it is also the more popular and prevalent language. Many people are able to pick it up much quicker than they would GTL or SSJS and find it a more comfortable language to work in. Therefore, for the email to be the most efficient and elegant it can be, it should be written in AMPscript (there are exceptions of course).

This is why BuildRowsetFromJSON() is so impactful. This function reduces the roadblocks to help ensure that your emails are the best they can be. It will ensure there is no need for added processing time by switching between languages nor is there any requirements for knowing, maintaining or utilizing any language in the email other than AMPscript, leading to a reduced technical knowledge pre-requisite.

As far as performance, BuildRowsetFromJSON() being used in place of an email built using efficient SSJS to parse and iterate through a JSON package saw a significant increase. You can see some performance based testing Mateusz Dabrowski did here, which shows the significant difference in processing between AMPscript and SSJS. This is not directly attributed to the BuildRowsetFromJSON() function, but I think it speaks strongly enough that we do not need to do a test specific to this function.

That being said, let’s take a look at the function and its properties:

BuildRowsetFromJSON(1,2,3)

Properties

Below are some links and helpful resources or playgrounds for you to learn and get better at utilizing this function:

JSONPath syntax (is used for property 2):
https://support.smartbear.com/alertsite/docs/monitors/api/endpoint/jsonpath.html

Great place to test JSONPathing for free online:
https://jsonpath.com/

Great place to play with the BuildRowsetFromJSON() function:
https://mcsnippets.herokuapp.com/

Some quick notes and tips:

The starting index of JSON for JSONPath is 0, but the starting index for Rowset it is 1. This means that the first iteration in a JSON array is 0, where inside of a Rowset (inside of AMPscript), the first iteration is 1. so Json[0] === Row(@myJSONRS,1)

With getting field values from the rowset, we have two options, Ordinal index or Key names. For example in the following JSON:

[{"Val1": "1","Val2": "2","Val3": "3"}]

We would use the following ( Row(@json,1)) to get the first, and only, row in the rowset so we would just need the individual fields. Below are the two options we have:

Ordinal: (Field(@row,1)) If we know where in the object it will be, but not sure the key that it would be stored under, we can use ordinal index instead which will get the first value inside of the row assigned – in this case it would be 1 from the key/value pair of "Val1":"1".

Key: (Field(@row,"Val2")) If we know the key, but not sure the ordinal number or prefer it explicitly by name, we can use the key instead which will output 2 as it is pulling from the key/value pair of "Val2":"2" from the object.

Filtering options in JSONPathing for BuildRowsetFromJSON() do not work. It seems currently that the ability in JSONPathing to use ?[] to filter the results is not available. I am sure more details on this will be released by SFMC on this in near future.

The third property (return empty or throw error) does not seem to make a difference. Even when putting a zero in this property, there are no errors thrown currently. Again this will likely be resolved in the near future and is just a ‘release bug’.

You cannot directly turn a single JSON Object into a rowset using JSON Path without first wrapping it as an array. More details on this and how to handle below.

Mixing Object and String Items in an array can have unexpected affects on ordinals for fields. More details on this below.

Handling the different types of JSON

Below is a quick example usage of BuildRowsetFromJSON() on different Array and Object scenarios.

JSON Object Only:

{"Val1": "1","Val2": "2","Val3": "3"}

With a base level JSON object, you will not be able to built a rowset from it directly. You will have 3 choices on paths to get what you need.

If you are looking for just a single value from the JSON, then you can use the following JSON Path to retrieve it: $.Val1, replacing Val1 with your desired key.

For example:

%%[
  SET @JSON = '{"Val1": "1","Val2": "2","Val3": "3"}'
  SET @Val1 = BuildRowsetFromJSON(@JSON,'$.Val1',1)
]%%

Outputs:

1

If you want to turn that Object into a full rowset instead, you will need to wrap it inside of brackets so that it becomes an Array of Objects. Ending up looking like:

[{"Val1": "1","Val2": "2","Val3": "3"}]

This can easily be done via a CONCAT, like below

VAR @JSON = CONCAT("[",@JSON,"]")

From there you can use the JSONPath of $.[*] to collect the row from that object and then use the Row and Field functions to collect the info you need.

For example:

%%[
  SET @JSON = '{"Val1": "1","Val2": "2","Val3": "3"}'
  SET @JSON = CONCAT('[',@JSON,']')
  SET @RS = BuildRowsetFromJSON(@JSON,'$.[*]',1)

  SET @Row = Row(@RS,1)
  SET @Val1 = Field(@Row,"Val1")
  SET @Val2 = Field(@Row,"Val2")
  SET @Val3 = Field(@Row,"Val3")
]%%

Outputs:

1
2
3

If you want to build each value as a different row, you will need to utilize the path of $.* which will take each value and create it as an individual row. So if you have a single object with 3 key/value pairs, it will be 3 rows long with each row being one of the key/value pairs.

For example:

%%[
SET @JSON = '{"Val1": "1","Val2": "2","Val3": "3"}'
SET @RS = BuildRowsetFromJson(@json_simple,'$.*',1)

FOR @i=1 TO Rowcount(@RS) DO
  SET @Row = Row(@RS,@i)
  SET @Val = Field(@Row,1)
]%%
  %%=v(@Val)=%%<br>
%%[
NEXT @i
]%%

Outputs:

1
2
3

JSON Object with a nested Array:

{"myArr": [{"Val1": "1","Val2": "2","Val3": "3"}]}

This is fairly easy, you essentially combine the above two methods for Object only into a single JSONPath to get it.

First you would need to access the key of myArr from the parent JSON Object, which means you would start with a JSONPath of $.myArr.

But if we leave it with just that, we will get an error as although we are grabbing the right Array now, we are not iterating through it. So to do that, we will need to add on .[*] to it so we can iterate through the array and build a rowset.

What we then would end up with is something like: $.myArr.[*] for our JSONPath. This will be what we need in order to access it.

For Example:

%%[
  SET @JSON = '{"myArr": [{"Val1": "1","Val2": "2","Val3": "3"}]}'
  SET @RS = BuildRowsetFromJson(@json,'$.myArr.[*]',1)

  SET @Row = Row(@RS,1)
  SET @Val1 = Field(@Row,"Val1")
  SET @Val2 = Field(@Row,"Val2")
  SET @Val3 = Field(@Row,"Val3")
]%%

Outputs:

1
2
3

JSON Array with a nested Object:

[{"Val1": "1","Val2": "2","Val3": "3"}]

This one is very simple, it is exactly the same method as we used in JSON Object Only above, but without needing to use the CONCAT() to add the brackets to it. From there you can use the JSONPath of $.[*] to collect the row from that object and then use the Row and Field functions to collect the info you need.

Along with that though, there are some unexpected behaviors that can happen from certain scenarios. Like for instance if your Array also has string items and not just objects.

[{"Val1": "1","Val2": "2","Val3": "3"},"Bob","John"]

This will make row 2 and 3 need to utilize the oridnal of 4 to get values (Bob/John) instead of 1 like what is normally expected.

If we did the following:

%%[
  SET @rows = BuildRowsetFromJson(@json,'$[*]',1)
  SET @RC = rowcount(@rows)
]%%
RowCount: %%=v(@RC)=%%<br>

%%[IF @RC > 0 THEN ]%%
Val1 = %%=v(Field(Row(@rows,1),1))=%%<br>
Val2 = %%=v(Field(Row(@rows,1),2))=%%<br>
Val3 = %%=v(Field(Row(@rows,1),3))=%%<br>
Bob = %%=v(Field(Row(@rows,2),4))=%%<br>
John = %%=v(Field(Row(@rows,3),4))=%%<br>
%%[ ENDIF ]%%

Outputs:

RowCount: 3

Val1 = 1
Val2 = 2
Val3 = 3
Bob = Bob
John = John

As you will note, for Row 2 and Row 3 (Bob and John in output) we had to push with ordinal 4 (object field count + 1) in order for the value to be displayed. I do not have a good reason for this, and hopefully it is just a ‘release bug’ as well and will be straighted out.

Also with the non-object prior to the Object, it causes some odd behavior:

["Hank",{"Val1": "1","Val2": "2","Val3": "3"},"Bob","John"]

This would make all the non-object values in the array/row be in ordinal 1, but the object would have to use ordinal 2, 3 and 4 instead of 1, 2 and 3 like normal.

For example:

%%[
  SET @json = '["Hank",{"Val1": "1","Val2": "2","Val3": "3"},"Bob","John"]'
  SET @rows = BuildRowsetFromJson(@json,'$[*]',1)
  SET @RC = rowcount(@rows)
]%%

RowCount: %%=v(@RC)=%%<br>

%%[IF @RC > 0 THEN ]%%
Hank = %%=v(Field(Row(@rows,1),1))=%%<br>
Val1 = %%=v(Field(Row(@rows,2),2))=%%<br>
Val2 = %%=v(Field(Row(@rows,2),3))=%%<br>
Val3 = %%=v(Field(Row(@rows,2),4))=%%<br>
Bob = %%=v(Field(Row(@rows,3),1))=%%<br>
John = %%=v(Field(Row(@rows,4),1))=%%<br>
%%[ ENDIF ]%%

As you notice from this, it is exactly opposite of how it is in the previous example. I believe that the string items in the array are counted ‘outside’ the fields in the object, which means, depending on where they are in the iterations, will determine if its the ordinal of (1 + Object Fields) or if the Object fields are pushed upwards one for each.

But if you do it with 2 objects and no string items, it works as expected.

[{"Val1": "1","Val2": "2","Val3": "3"},{"Val1": "Hank", "Val2":"Bob","Val3":"John"}]

This will have the correct ordinal numbers on it and allow you to loop through equally.

For example:

%%[
  SET @json = '[{"Val1": "1","Val2": "2","Val3": "3"},{"Val1": "Hank", "Val2":"Bob","Val3":"John"}]'
  SET @rows = BuildRowsetFromJson(@json,'$[*]',1)
  SET @RC = rowcount(@rows)
]%%

RowCount: %%=v(@RC)=%%<br>

%%[IF @RC > 0 THEN ]%%
Val1 = %%=v(Field(Row(@rows,1),1))=%%<br>
Val2 = %%=v(Field(Row(@rows,1),2))=%%<br>
Val3 = %%=v(Field(Row(@rows,1),3))=%%<br>
Hank = %%=v(Field(Row(@rows,2),1))=%%<br>
Bob = %%=v(Field(Row(@rows,2),2))=%%<br>
John = %%=v(Field(Row(@rows,2),3))=%%<br>
%%[ ENDIF ]%%

Outputs:

RowCount: 2

Val1 = 1
Val2 = 2
Val3 = 3
Hank = Hank
Bob = Bob
John = John

There are a ton of more scenarios and other ways to utilize this function, but the above should get you to a good place and with practice, you should be able to delve into any JSON using this function and get the info you want. Feel free to leave comments or connect with me via HowToSFMC.com Slack community or in EmailGeeks Slack community. Always happy to chat!