BuildRowsetFromJSON

BuildRowSetFromJSON

This function creates a row set from an JSON string. It returns an error or an empty row set if there no matches the specified JSON path.

Arguments

BuildRowsetFromJSON(1,2,3)

NOTE: As with XML, AMPscript treats JSONs as string values until parsed into row sets.

NOTE: According to Salesforce, this function follows the Internet Engineering Task Force (IETF) specifications, outlined in these examples.

NOTE: The current version of Salesforce Marketing Cloud does not support JSON path filter expressions. An expression like $.order.lineItems.[?(@.name=='Square')] (against the sample JSON below) is unsupported. More information about JSON path filter expressions can be found here.

NOTE: The third argument is required, however it does not return an error in any non-match scenario — only an empty rowset.

TIP: For help building a valid JSON path, see JSONPath Online Evaluator, JSON Path Finder or JSONPath Expression Tester.

Example 1

This example returns the order line items in a JSON payload and outputs the properties of each.

%%[

  set @jsonStr = AttributeValue("jsonStr") /* value from attribute or DE column in send context */
  set @jsonStr = "" /* or a literal value */

  set @jsonStr = '{
    "order": {
      "orderNum": 123456789,
      "firstName": "Dietrich",
      "lineItems": [
        {
          "sku": "123",
          "name": "Square",
          "url": "https://limedash.com?sku=123"
        },
        {
          "sku": "456",
          "name": "Circle",
          "url": "https://limedash.com/?sku=456"
        },
        {
          "sku": "789",
          "name": "Triangle",
          "url": "https://limedash.com/?sku=789"
        }
      ]
    }
  }'

  set @lineItemRowset = BuildRowsetFromJSON(@jsonStr,"$.order.lineItems.[*]",1)
  set @rowCount = rowcount(@lineItemRowset)

  if @rowCount > 0 then

    for @i = 1 to @rowCount do

      set @row = row(@lineItemRowset, @i)
      set @sku = field(@row, "sku")
      set @name = field(@row, "name")
      set @url = field(@row, "url")

      ]%%

      <br><br>row: %%=v(@i)=%%
      <br>sku: %%=v(@sku)=%%
      <br>name: %%=v(@name)=%%
      <br>url: %%=v(@url)=%%

      %%[

    next @i

  else

   output(concat("<br>no line items found"))

  endif

]%%

Output

row: 1
sku: 123
name: Square
url: https://limedash.com?sku=123

row: 2
sku: 456
name: Circle
url: https://limedash.com/?sku=456

row: 3
sku: 789
name: Triangle
url: https://limedash.com/?sku=789

Example 2

Example 3