BuildRowSetFromJSON

Use this function to load JSON data into a rowset. This function is helpful if you have JSON data that you want to programmatically insert into your content. This function is executed at send time for outgoing messages and at load time for CloudPages.

For more information about rowsets, see the Rowsets section of the AMPscript Language Elements documentation.

BuildRowSetFromJSON(1, 2, 3)

This function implements the draft JSONPath specification from ietf.org.

Pass a valid JSONPath expression for the second ordinal. The JSONPath expression that you specify can use bracket notation or dot notation.

If the JSONPath expression produces values, the rowset contains a "Value" column with the value of the field that you queried.

If the JSON path expression produces objects, the fields of those objects are returned as columns of the rowset. The values of those columns are the values in that object. Exceptions to this rule occur when the value is an object or array type. In these cases, the value is "JSON Object" or "JSON Object Array", respectively.

Consider a JSON object that contains this data.

You can use the BuildRowsetFromJson function to obtain the objects in the "Flights" array using AMPscript.

When your content includes this code, the system returns a rowset that contains this data.

If you only need the content in a specific column of the rowset, modify the JSONPath expression to refer to the key name that corresponds to that row. For example, if you only want the Price data, use this AMPscript code.

When your content includes this code, the system returns a rowset that contains this data.

If the function contains a syntax error, it returns an exception. A few circumstances result in an exception.

• The function doesn’t include the required number of ordinals.
• The value of the first ordinal contains JSON data with invalid syntax and the value of the third ordinal is 0 or false.
• The value of the second ordinal contains a JSONPath argument with invalid syntax and the value of the third ordinal is 0 or false.
• The value of the third ordinal contains anything other than a number or boolean.

If the JSONPath expression doesn’t produce any data, the function returns an empty row. The function returns an empty rowset in these situations.

• The JSONPath expression is valid but refers to an object that doesn’t exist in the source data.
• The JSONPath expression is valid, but the JSON source data is invalid, and the value of the third ordinal is 1 or true.

To handle cases in which the function returns empty rows, you can use the RowCount function to check the number of rows that the BuildRowSetFromJSON function returns. You can then use If and Else statements to display a fallback message when the function returns an empty rowset. The code example in the next section shows how to handle this situation.

You can use the BuildRowSetFromJSON function to parse JSON data and insert the data that it contains into your HTML. This example performs some basic validation and formatting to handle situations where the input data is inconsistent. It also checks to make sure that the value that the BuildRowSetFromJSON function provides isn’t empty and prints an error message if it’s empty. Finally, the code creates an HTML table using the JSON data.

The rendered HTML contains a table that resembles this example.