Our JPL implementation, called "ASQ", is not very fast (yet, we are working on that) but it works well for small sets of data.

LATEST:USER>:pp obj.apply("$[*].items[*].externalPositions[*]")
do ##class(%ASQ.SetUtils).pp(obj.apply("$[*].items[*].externalPositions[*]"))
[
  {
    "pureId": 0,
    "appointment": {
      "uri": "string",
      "term": {
        "en_GB": "Some text"
      }
    },
    "appointmentString": {
      "en_GB": "Some text"
    },
    "period": {
      "startDate": {
        "year": 0,
        "month": 1,
        "day": 1
      },
      "endDate": {
        "year": 0,
        "month": 1,
        "day": 1
      }
    },
    "externalOrganization": {
      "uuid": "196ab1c9-6e60-4000-8b89-29269178a480",
      "systemName": "string"
    }
  }
]

I agree - $system.external is the correct interface. You should be able to find out more about the $system.external interface by calling $system.external.Help().

Dynamic SQL doesn't have to build the query solution every time you run the same statement. Once you initially prepare an SQL Statement, the implementation of that statement is cached and reused. The only overhead should be limited to a hopefully very brief cache resolve. There are ways that you can write a statement that makes the hashing algorithm we use very inefficient (prepare:execute ratio p:n, the larger 'n' is, the better - 1:1 being the worst). Since we now use a unified model, even embedded SQL will produce a cached implementation. Embedded reduces the cache resolve time.