BuildRowSetFromJson()

Loads JSON data into a rowset. Use this function 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.

The BuildRowSetFromJson() function has three parameters:

  • jsonData (string): Required. The JSON data that you want to parse.
  • jsonPathExpression (string): Required. The JSONPath expression that parses the source data.
  • boolReturnEmptyOnError (boolean): Required. If false, the function returns an empty rowset when there’s a syntax error in the function or the JSONPath expression. If true, the function returns an exception when an error occurs.

The value of the jsonData parameter can’t be a simple JSON object such as {"first": "John", "last": "Smith", "country": "US"}.

Pass a valid JSONPath expression for the jsonPathExpression parameter. The JSONPath expression that you specify can use bracket notation or dot notation. This function doesn’t support JSONPath filter expressions.

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

If the JSONPath 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.

To obtain the objects in the Flights array using AMPscript, use the BuildRowsetFromJson() function.

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

OriginDestPricePerBagSurcharge
INDNYC100
INDLAX200
INDSEA50025

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.

Value
100
200
500

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 parameters.
  • The value of the first parameter contains JSON data with invalid syntax and the value of the third parameter is 0 or false.
  • The value of the second parameter contains a JSONPath argument with invalid syntax and the value of the third parameter is 0 or false.
  • The value of the third parameter 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 parameter 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.

OriginDestinationPricePer bag Surcharge
INDNYC$100.00$0.00
INDLAX$200.00$0.00
INDSEA$500.00$25.00