# 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. ## Availability
Marketing Cloud Engagement ✅ Yes
Marketing Cloud Next ✅ Yes
This function became available in Marketing Cloud Next during the Summer ’26 release (API version 67.0). ## Syntax ``` BuildRowSetFromJson(jsonData, jsonPathExpression, boolReturnEmptyOnError) ``` 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. ## Usage The `jsonData` parameter must contain JSON that the `jsonPathExpression` can resolve to a collection of values or objects. A flat JSON object such as `{"first": "John", "last": "Smith", "country": "US"}` doesn’t work on its own, because the function builds a rowset and a single object has nothing to iterate over. To work with a flat object, wrap it in an array and use a JSONPath expression that selects the array elements. For example, this AMPscript code produces a one-row rowset with `first`, `last`, and `country` columns. ``` Set @json = '[{"first": "John", "last": "Smith", "country": "US"}]' Set @rows = BuildRowSetFromJson(@json, '$[*]', 1) ``` If you only need the values from a single object and don’t need a rowset, set them directly as AMPscript variables instead of calling `BuildRowSetFromJson()`. 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. ```json { "Flights": [ { "Origin": "IND", "Dest": "NYC", "Price": 100.0 }, { "Origin": "IND", "Dest": "LAX", "Price": 200.0 }, { "Origin": "IND", "Dest": "SEA", "Price": 500.0, "PerBagSurcharge": 25 } ] } ``` To obtain the objects in the `Flights` array using AMPscript, use the `BuildRowsetFromJson()` function. ``` BuildRowsetFromJson(@json, '$.Flights[*]', 1) ``` When your content includes this code, the system returns a rowset that contains this data. | Origin | Dest | Price | PerBagSurcharge | | ------ | ---- | ----- | --------------- | | IND | NYC | 100 | | | IND | LAX | 200 | | | IND | SEA | 500 | 25 | 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. ``` BuildRowsetFromJson(@json, '$.Flights[*].Price', 1) ``` When your content includes this code, the system returns a rowset that contains this data. | Value | | ----- | | 100 | | 200 | | 500 | ## Errors 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. ## Code example 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. ``` %%[ Set @json = ' { "Flights": [ { "Origin": "IND", "Dest": "NYC", "Price": 100.0, "PerBagSurcharge": "N/A" }, { "Origin": "IND", "Dest": "LAX", "Price": 200.0, "PerBagSurcharge": "Free" }, { "Origin": "IND", "Dest": "SEA", "Price": 500.0, "PerBagSurcharge": 25 } ] }' Set @rows = BuildRowsetFromJSON(@json, "$.['Flights'][*]", true) Set @currency_locale = "en-US" /* Make sure that at least 1 row of data is returned. If not, show an error message. */ Set @rowcount = RowCount(@rows) If @rowcount > 0 THEN ]%% %%[ For @row = 1 TO RowCount(@rows) do ]%% %%[ For @col = 1 TO 2 do Set @value = Field(Row(@rows, @row), @col) ]%% %%[ next @col /* Handle columns 3 and 4 differently because we’re expecting a dollar value for our HTML output. */ For @col = 3 TO 4 do Set @value = Field(Row(@rows, @row), @col) /* Check to see if @value contains a number. */ Set @pattern = "^[.]*[0-9].*[0-9]" Set @match = RegExMatch(@value, @pattern, 0) /* If @value contains anything other than a number, set the value to 0. If there’s a better default value, specify it here. */ If Empty(@match) then Set @value = 0 EndIf /* Use the FormatCurrency function to format the resulting value as a currency value. */ Set @value = FormatCurrency(@value, @currency_locale) ]%% %%[ next @col ]%% %%[ next @row ]%%
Origin Destination Price Per bag Surcharge
%%= v(@value) =%%%%= v(@value) =%%
%%[ else ]%%

Well that’s embarrassing!

We weren’t able to find any pricing information. Check again later.

%%[ EndIf ]%% ``` The rendered HTML contains a table that resembles this example. | Origin | Destination | Price | Per bag Surcharge | | ------ | ----------- | ------- | ----------------- | | IND | NYC | $100.00 | $0.00 | | IND | LAX | $200.00 | $0.00 | | IND | SEA | $500.00 | $25.00 |