Nesting JSON templates being treated as strings

I’m trying to capture an Inventory Snapshot. I’m creating a JSON of the values in my inventory row (product, quantity, location etc) using the JSON Template column.
In another JSON template column I’m going to wrap these all up as an array to create the complete Inventory Snapshot with this template:

{"date": $date_val, "Inventory": [$inventory_val]}

I create a joined list of all the row JSONs so that I can create the array of inventory items which is substituted for $inventory_val in the template.

All of the components seem to work fine, until I look at the final output, in which case I get something like this:

  "Date": "2025-01-17T12:12:43.340Z",
  "Inventory": [
    "{\"Location\":\"D9-1\",\"Is_Empty\":false,\"Pallet_Number\":\"P0093\",\"Description\":\"TD Stock\"}, {\"Location\":\"D11-1\",\"Is_Empty\":true,\"Pallet_Number\":\"P0130\",\"Description\":\"Corks / Blue Sleeves\",\"Quantity\":0}, {\"Location\":\"A5-4\",\"Is_Empty\":false,\"Pallet_Number\":\"P0137\",\"Description\":\"Unusable Space\"}

I asked Claude for help to understand what this was called and he said this:

The problem is that the inventory array contains JSON strings of the rows, rather than the actual JSON objects. This means each row is being treated as a single string (with escaped quotes) rather than as a JSON object

So I have a description of the problem, but I have no idea how to deal with it.

Anyone got any ideas?

Use a lookup instead of a joined list.

1 Like

Dude… you’re obviously a legend here, but now you’re my legend :rofl:

I’ve not had any circumstances where a lookup array has been able to be used as text in anything until now - only ever as an input to some computed column that parses arrays. I wish there was more documentation about that, but it is what it is.

Thank you!

I may be pushing my luck, but if you have any other tips or tricks for when an array can be used as an input I’d love it!

Yeah, this is not mentioned in the documentation, which is a bit of an oversight I guess. It’s just one of those things that once you know it, you know it :wink:

Nothing that comes immediately to mind. In this case, your JSON Template is expecting an array, and that’s what the Lookup column provides. A Joined List is not strictly an array.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.