Add, update and remove JSON array items in FileMaker

1. Start with an explicit empty array.

JSONSetElement ( "[]" ;
    [ "[0].sku" ; "P-001" ; JSONString ] ;
    [ "[0].qty" ; 2 ; JSONNumber ] ;
    [ "[1].sku" ; "P-002" ; JSONString ] ;
    [ "[1].qty" ; 1 ; JSONNumber ]
)

Save this result in a script variable called $lines. The first element is at index zero. Keep the SKU as text and the quantity as a number; a formatter can show those types, but it cannot decide which type your receiving API expects.

[{"sku":"P-001","qty":2},{"sku":"P-002","qty":1}]

Choose the type deliberately. A product code that happens to contain only digits can still be an identifier. Treating “0007” as a number would lose meaningful leading zeroes.

2. Change only the intended quantity.

JSONSetElement ( $lines ; "[1].qty" ; 4 ; JSONNumber )

Assign the returned text back to $lines; the function does not mutate the original variable by itself. The expected quantities are now 2 and 4. P-001 remains unchanged. Read JSONGetElement ( $lines ; "[1].sku" ) before updating if you are investigating which item occupies that position.

For a UI that stores a selected SKU, locate that SKU in the current array before updating. An index remembered from an earlier version of the array may no longer identify the same item. Decide how to handle duplicate SKUs if duplicates are allowed.

3. Delete, then check the new positions.

JSONDeleteElement ( $lines ; "[0]" )

Again, store the returned result. After the update above and this deletion, the array contains only P-002 with quantity 4:

[{"sku":"P-002","qty":4}]

P-002 is now at index zero. Do not continue to use index one for that line. If a workflow removes several items by position, collecting the positions first and deleting from the highest index downward avoids shifting the lower positions before you reach them. Test that strategy separately before using it with customer data.

4. Separate an empty list from the wrong shape.

An empty array [] is a valid list with no items. An empty object {} is a different structure. On 19.5 or later, check JSONGetElementType ( $lines ; "" ) = JSONArray before assuming array operations are appropriate.

For trusted, validated array input, JSONListKeys ( $lines ; "" ) gives the positions you can inspect. Do not use a missing field as an instruction to create a new line without confirming that behavior with the calling workflow.

• Two items, update the second: first item stays unchanged.
• Remove the first: second item becomes index zero.
• One item, remove it: expect [].
• Malformed JSON or an object instead of an array: reject it before applying the update.

5. Compare the structure, not the whitespace.

Use the JSON formatter to inspect a fictional before-and-after sample. Formatting can change spacing and object-key order without changing the values. Compare SKU, quantity, type and array order rather than the entire formatted string.

When you connect the result to an API, confirm whether the body should be a root array or an object containing an array, such as {"lines":[...]}. That is part of the API contract. A syntactically valid root array can still be the wrong request body.

Continue reading

• Create JSON from FileMaker fields
• Read a JSON API response in FileMaker