BuildRowsetFromJSON

Parses a JSON string and returns a rowset. Runtime-proven on a live Marketing Cloud Engagement CloudPage — including the third argument, which works the opposite way round from the documented syntax.

Engagement Next

Runtime verified Differs from official docs Test scripts included

Syntax

BuildRowsetFromJSON(jsonData, jsonPathExpression, returnEmptyOnError)  →  rowset

3 arguments — exactly

Parameters

Name	Type	Required	Description
jsonData	string	Yes	The JSON payload to parse
jsonPathExpression	string	Yes	JSONPath expression selecting the nodes to turn into rows
returnEmptyOnError	boolean | number	Yes	Pass 1 or true for an empty rowset when the payload or path cannot be parsed

Example

%%[
  VAR @json, @rows
  SET @json = '{"Flights":[{"Origin":"IND","Dest":"NYC","Price":100.0},{"Origin":"IND","Dest":"LAX","Price":200.0},{"Origin":"IND","Dest":"SEA","Price":500.0}]}'
  SET @rows = BuildRowsetFromJSON(@json, "$.Flights[*]", 1)
]%%
Rows: %%=v(RowCount(@rows))=%% first: %%=v(Field(Row(@rows, 1), "Dest"))=%%

Renders Rows: 3 first: NYC.

Because untrusted input yields an empty rowset rather than a value you can test, guard the read with RowCount and use the three-argument Field form for columns that may be absent:

%%[
  VAR @rows, @row
  SET @rows = BuildRowsetFromJSON(@payload, "$.Flights[*]", 1)
  IF RowCount(@rows) > 0 THEN
    SET @row = Row(@rows, 1)
]%%
    Surcharge: %%=v(Field(@row, "PerBagSurcharge", 0))=%%
%%[ ENDIF ]%%

Return value

rowset — one row per node the JSONPath expression matched.

There is no closed set of sentinel values: an unparsable payload, an empty string, an unset variable, an empty array and a path that matches nothing all produce a rowset of zero rows when the third argument is 1, so RowCount is the only value worth branching on.

Behaviour

A path selecting an array of objects gives one row per element, with the object keys as named columns. Selecting $.Flights[*] over a three-element array produced three rows, and the first row’s Origin, Dest and Price columns read back as IND, NYC and 100.

Column names are case-insensitive, and columns are also addressable by 1-based ordinal. Reading dest returned the same value as Dest, and ordinals 1 and 2 on the first row gave IND and NYC. A column that only some elements carry reads normally on the elements that have it.

A trailing decimal zero is dropped. The JSON literals 100.0 and 200.0 rendered as 100 and 200.

A path selecting scalars gives a single column named Value. $.Flights[*].Price produced three rows whose second row read 200 both as Value and as ordinal 1. A wildcard path over a single object gives one row per key, and a nested scalar array gives one row per element — both read by ordinal 1.

Structured values render empty rather than as a placeholder label. Selecting the keys of an object whose values are themselves an object and an array gave three rows in which both structured cells were empty. The finding is catalogued on Differs from official docs.

Rowsets are read 1-based. Row(rowset, 0) and Field(row, 0) both abort the page. Field(row, "<missing column>") aborts too, while the three-argument form Field(row, "<missing column>", 0) renders an empty string — prefer that form for anything that may be absent. RowCount never aborts and returns 0 for an empty rowset.

Either capitalisation resolves to the same function. BuildRowSetFromJSON with a capital S produced identical results.

The third argument is inverted

This contradicts the official documentation

The Syntax section presents a false third argument as the one that returns an empty rowset and a true one as the one that raises. At runtime it is the other way round.

Call	Result
BuildRowsetFromJSON("{ not json ", "$.Flights[*]", 1)	rowset with zero rows
BuildRowsetFromJSON("{ not json ", "$.Flights[*]", 0)	HTTP 422, page aborted

The boolean literal true behaves exactly like the number 1. Read the argument as return empty on error: pass 1 (or true) for any payload you do not control, then branch on RowCount. The same page’s Errors section already describes the runtime ordering, so the reference contradicts itself. The full finding is on Differs from official docs, and the XML sibling behaves identically.

Show test script

%%[
  VAR @b, @json, @single, @nestarr, @mixed, @unset, @rows, @r1, @r3
  SET @b = RequestParameter("b")
  SET @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}]}'
  SET @single = '{"Id":123,"Shape":"Square"}'
  SET @nestarr = '{"Shapes":["Triangle","Circle","Square"]}'
  SET @mixed = '{"a":{"b":1},"c":[1,2],"d":"plain"}'

  /* control branch: proves the page itself renders */
  IF @b == "zzz" THEN
    OutputLine(Concat("CTRL=[alive]"))
  ENDIF

  /* array of objects: row per element, named columns, case-insensitive names, ordinals */
  IF @b == "safe" THEN
    SET @rows = BuildRowsetFromJSON(@json, "$.Flights[*]", 1)
    SET @r1 = Row(@rows, 1)
    SET @r3 = Row(@rows, 3)
    OutputLine(Concat("j1 rc=[", RowCount(@rows), "]"))
    OutputLine(Concat("j1 r1 byname Origin=[", Field(@r1, "Origin"), "] Dest=[", Field(@r1, "Dest"), "] Price=[", Field(@r1, "Price"), "]"))
    OutputLine(Concat("j1 r1 ord1=[", Field(@r1, 1), "] ord2=[", Field(@r1, 2), "]"))
    OutputLine(Concat("j1 r3 byname Dest=[", Field(@r3, "Dest"), "] PerBagSurcharge=[", Field(@r3, "PerBagSurcharge", 0), "]"))
    OutputLine(Concat("j1 r3 lowercase-name dest=[", Field(@r3, "dest"), "]"))
  ENDIF

  /* scalar path: single column named Value, also ordinal 1 */
  IF @b == "scalars" THEN
    SET @rows = BuildRowsetFromJSON(@json, "$.Flights[*].Price", 1)
    OutputLine(Concat("j5 rc=[", RowCount(@rows), "]"))
    OutputLine(Concat("j5 r2 Value=[", Field(Row(@rows, 2), "Value", 0), "] ord1=[", Field(Row(@rows, 2), 1), "]"))
  ENDIF

  /* single object with a wildcard path: one row per key */
  IF @b == "singleobj" THEN
    SET @rows = BuildRowsetFromJSON(@single, "$.*", 1)
    OutputLine(Concat("j6 rc=[", RowCount(@rows), "]"))
    OutputLine(Concat("j6 r1 ord1=[", Field(Row(@rows, 1), 1), "] r2 ord1=[", Field(Row(@rows, 2), 1), "]"))
  ENDIF

  /* nested scalar array: one row per element */
  IF @b == "nestedarr" THEN
    SET @rows = BuildRowsetFromJSON(@nestarr, "$.Shapes[*]", 1)
    OutputLine(Concat("j7 rc=[", RowCount(@rows), "]"))
    OutputLine(Concat("j7 r2 ord1=[", Field(Row(@rows, 2), 1), "] Value=[", Field(Row(@rows, 2), "Value", 0), "]"))
  ENDIF

  /* structured values under a wildcard path render empty */
  IF @b == "nested" THEN
    SET @rows = BuildRowsetFromJSON(@mixed, "$.*", 1)
    OutputLine(Concat("j18 rc=[", RowCount(@rows), "] r1=[", Field(Row(@rows, 1), 1), "] r2=[", Field(Row(@rows, 2), 1), "] r3=[", Field(Row(@rows, 3), 1), "]"))
  ENDIF

  /* everything that yields zero rows while the third argument is 1 */
  IF @b == "empties" THEN
    OutputLine(Concat("j8 malformed-flag1 rc=[", RowCount(BuildRowsetFromJSON("{ not json ", "$.Flights[*]", 1)), "]"))
    OutputLine(Concat("j10 emptystring-flag1 rc=[", RowCount(BuildRowsetFromJSON("", "$.Flights[*]", 1)), "]"))
    OutputLine(Concat("j11 emptyarray rc=[", RowCount(BuildRowsetFromJSON("[]", "$[*]", 1)), "]"))
    OutputLine(Concat("j12 unsetvar-flag1 rc=[", RowCount(BuildRowsetFromJSON(@unset, "$.Flights[*]", 1)), "]"))
    OutputLine(Concat("j13 nomatch rc=[", RowCount(BuildRowsetFromJSON(@json, "$.NoSuchKey[*]", 1)), "]"))
  ENDIF

  /* the boolean literal true behaves like the number 1 */
  IF @b == "boolflag" THEN
    OutputLine(Concat("j14 boolliteral rc=[", RowCount(BuildRowsetFromJSON(@json, "$.Flights[*]", true)), "]"))
  ENDIF

  /* the alternate capitalisation resolves to the same function */
  IF @b == "casing" THEN
    OutputLine(Concat("j17 capitalS-casing rc=[", RowCount(BuildRowSetFromJSON(@json, "$.Flights[*]", 1)), "]"))
  ENDIF

  /* the three-argument Field form renders empty instead of aborting */
  IF @b == "missingcol3" THEN
    SET @rows = BuildRowsetFromJSON(@json, "$.Flights[*]", 1)
    OutputLine(Concat("j4 missingcol3arg=[", Field(Row(@rows, 1), "NoSuchColumn", 0), "]"))
  ENDIF

  /* fetch ?b=abortflag0 - HTTP 422, nothing renders */
  IF @b == "abortflag0" THEN
    OutputLine(Concat("--- j9 start ---"))
    SET @rows = BuildRowsetFromJSON("{ not json ", "$.Flights[*]", 0)
    OutputLine(Concat("j9 rc=[", RowCount(@rows), "]"))
  ENDIF

  /* fetch ?b=abortmissingcol - HTTP 422, the two-argument Field form on a missing column */
  IF @b == "abortmissingcol" THEN
    OutputLine(Concat("--- j3 start ---"))
    SET @rows = BuildRowsetFromJSON(@json, "$.Flights[*]", 1)
    OutputLine(Concat("j3 missingcol2arg=[", Field(Row(@rows, 1), "NoSuchColumn"), "]"))
  ENDIF
]%%

OutputLine needs Concat

OutputLine given a bare string literal renders an empty line. Wrap the argument in Concat() or your start and done markers vanish silently — which looks exactly like the function failing.

Availability

Platform	Available
Marketing Cloud Engagement	Yes
Marketing Cloud Next	Yes, from API 67.0

See also

• Differs from official docs — the inverted third argument in full
• BuildRowSetFromXML — the same shape for XML payloads, with the same inverted flag
• BuildRowSetFromString — splits a delimited string into a rowset
• Official reference · ampscript.guide