@Herman Slagman - Perhaps we will meet again and we can have a vigorous debate! Your turn to buy ;)

I am somewhat reluctant to show this as the performance is not great. We are working on that. Also, keep in mind that the expression language is JSON Path Language which was made an ISO Standard in the 2016 SQL Standard. We have extended it a bit and also implemented it in a way that allows it to be used outside of SQL which is not part of the Standard.

In this snippet, the lvar 'j' is assigned the value of the JSON posted by the OP. I modified it to make it valid JSON (just added quotes here and there).

do ##class(%ASQ.SetUtils).pp(j.apply("$.items[*].identifiers[*]?(@.typeDiscriminator == 'ClassifiedId' && @.type.term.en_US == 'Scopus Author ID')"))

The result:

[
  {
    "typeDiscriminator": "ClassifiedId",
    "pureId": "xxxxxxxx",
    "id": "xxxxxxxx",
    "type": {
      "uri": "/dk/atira/pure/person/personsources/scopusauthor",
      "term": {
        "en_US": "Scopus Author ID"
      }
    }
  }
]

The performance of apply() can be improved significantly by parsing the expression first and then passing the parsed result to apply(). For one-off executes that isn't helpful but if you are applying the same expression to different data then the improvement is significant.

LATEST:USER>set ast = ##class(%ASQ.Parser).parse("$.items[*].identifiers[*]?(@.typeDiscriminator == 'ClassifiedId' && @.type.term.en_US == 'Scopus Author ID')")

LATEST:USER>set result = j.apply(ast)

LATEST:USER>do ##class(%ASQ.SetUtils).pp(result)
[
  {
    "typeDiscriminator": "ClassifiedId",
    "pureId": "xxxxxxxx",
    "id": "xxxxxxxx",
    "type": {
      "uri": "/dk/atira/pure/person/personsources/scopusauthor",
      "term": {
        "en_US": "Scopus Author ID"
      }
    }
  }
]

You can also do this in SQL but it is quite verbose. You have to map the JSON values to columns in the SELECT. I can provide an example if it would be helpful.

Perhaps I should have been more clear. I was responding to Enrico's comment about sequencing properties as they are stored in a global.

Back to your initial question, what is your definition of "sorted by Storage"?

If you need the ($list) position of properties within the global, then your query does not answer your question. I'm writing this in case other community members read this question/answer.

In response to my good friend Enrico's comment about "storage":

SELECT cc.Name as class_name,cs.Name as storage_name,cd.Name as data_node_name,cd.structure as structure, cd.subscript as subscript,cd.attribute as attribute, cv.name as value_name, cv.value as value
  FROM %Dictionary.CompiledClass cc
    JOIN %Dictionary.CompiledStorage cs on cc.%ID = cs.parent
    JOIN %Dictionary.CompiledStorageData cd on cs.%ID = cd.parent
    LEFT OUTER JOIN %Dictionary.CompiledStorageDataValue cv on cd.%ID = cv.parent
 WHERE cc.name = 'User.Person'

And a general comment about dynamic SQL. In the early days of Caché, someone made the decision to return a status value from methods to indicate success/failure. We didn't have try/catch at the time so it made some sense. Now we have try/catch (regrettably, no finally) and dynamic SQL follows a pattern that I started using many years ago - the traditional %<capital case> methods return a %Status value and a non-percent, lower camelCase method throws an exception. For dynamic SQL, that is prepare() and execute().

The primary benefit in using the exception-throwing method interface is that you cannot completely ignore errors unless you use a specific pattern to do so.

LATEST:USER>zw sql
sql=6
sql(1)="SELECT cc.Name as class_name,cs.Name as storage_name,cd.Name as data_node_name,cd.structure as structure, cd.subscript as subscript,cd.attribute as attribute, cv.name as value_name, cv.value as value"
sql(2)="  FROM %Dictionary.CompiledClass cc"
sql(3)="    JOIN %Dictionary.CompiledStorage cs on cc.%ID = cs.parent"
sql(4)="    JOIN %Dictionary.CompiledStorageData cd on cs.%ID = cd.parent"
sql(5)="    LEFT OUTER JOIN %Dictionary.CompiledStorageDataValue cv on cd.%ID = cv.parent"
sql(6)=" WHERE cc.name = 'User.Person'"

LATEST:USER>do statement.prepare(.sql)

LATEST:USER>set result = statement.execute()

LATEST:USER>do result.%Display()
class_name    storage_name    data_node_name    structure    subscript    attribute    value_name    value
User.Person    Default    DescriptionNode    node    d    description
User.Person    Default    PersonDefaultData    listnode            1    name
User.Person    Default    PersonDefaultData    listnode            2    dob
User.Person    Default    PersonDefaultData    listnode            3    address
User.Person    Default    PersonDefaultData    listnode            4    counter

5 Rows(s) Affected

Previous version of my reply was for the storage order for data stored. Sorry.

The compute triggers, %%INSERT and %%UPDATE are, in my opinion the best option. They are specifically supported for this purpose and are completely seamless between SQL and Objects. Callbacks and other options may not be seamlessly supported by both filers.

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.

@Andre Larsen Barbosa - I am the author of the intiial dynamic SQL 'feature'. I no longer work in this area and many significant enhancements have been made since my initial work. That said, I applaud your article. It is quite nice.

There is one feature that not many people know about - there is an Object mode that you can use with Dynamic Statement. Object mode is interesting when your query selects column values whose underlying type is a IRIS ObjectScript Class that is an object type (Persistent, Serial, Stream). Here is a trivial example of how it might be used.

LATEST:USER>set statement = ##class(%SQL.Statement).%New()

LATEST:USER>set statement.%ObjectSelectMode = 1

LATEST:USER>do statement.prepare("select name,address from person")

LATEST:USER>set result = statement.execute()

LATEST:USER>write result.%Next()
1
LATEST:USER>write result.name
po1
LATEST:USER>write result.address
7@User.Address
LATEST:USER>write result.address.city
Boston
LATEST:USER>write result.address.state
MA

    set stream = ##class(%Stream.FileCharacter).%OpenId("/Users/.../data/continents-en.json")

Did you know that you can just open a file as a stream?

And for iterating - this has been in the product for a while now. This is the code for my :pp alias.

ClassMethod pp(set As %AbstractSet, offset As %Integer = 0)
{
#define QUOTE(%val) $zu(144,1,%val)
    try {
        set isLabeled = set."_isLabeled"()
        if (isLabeled) {
            write "{"
            set close = "}"
        } else {
            write "["
            set close = "]"
        }
        set it = set.iterator()
        while it.hasNext() {
            set next = it.next()
            if $isobject(next.value) {
                write !,?(offset+2)
                write:isLabeled $$$QUOTE(next.key),": "
                do ..pp(next.value, offset + 2)
            } else {
                write !,?(offset+2),$select(isLabeled:$$$QUOTE(next.key)_": ",1:""),$$$QUOTE(next.value)
            }
            if it.hasNext() {
                write ","
            }
        }
        write !,?offset,close
    } catch exc {
        write !,"Exception caught: ",exc.AsSQLMessage()
    }
    return
}

This is in %ASQ.SetUtils. My alias, pp - pretty print, is this:

pp        do ##class(%ASQ.SetUtils).pp($*)

Basically the same as Enrico's:

LATEST:USER>set result = $system.SQL.Execute("select * from %SYS.Namespace_List()")

LATEST:USER>do result.%Display()
Nsp    Status    Remote
%SYS    1    0
B360    1    0
USER    1    0

3 Rows(s) Affected

The PackageDefinition class contains only packages where we need to keep metadata about a package. Most packages do not have any extra metadata.

The SQL solution is the correct solution IMO. If I were a developer on the Object team I would advocate for a method in $system.OBJ - PackageExists(<package_name>) perhaps. :)

I was tempted to criticize the Mixtral response. I did implement the original versions of both of these items so I know them both well. The Mixtral code example for %SQL.Statement is incorrect in a few ways but I'm surprised that it was as close as it is.

Once that temptation was put aside, I realized that Mixtral can only analyze what it finds. If it can't find the solution then perhaps our class documentation is not going to provide the answer to a user. Hmmm...

Actually, the problem is that %Exception.AbstractException already has five parameters. The error message you received is because the fifth argument has a type of %Exception.AbstractException and your fifth argument has a type of %String.

It is entirely possible to override the implementation of a member inherited from a super class so long as the inherited member is not defined as final and the signature of the local override is compatible with the inherited signature. The class compiler does perform a signature check to ensure the class does not violate the prime directive: Every instance of a class is implicitly a valid instance of its primary super class. Rewording that slightly - the interface of a class must be compatible with the interface of its primary super class. You can add new parameters to an inherited method but the existing parameters, those inherited, must declare a compatible type. A compatible type can be more specific (a subclass of the inherited type) but it cannot be a competely different type.

I tried adding a sixth parameter and also corrected the type of the fifth:

This class compiled cleanly.

ObjectScript does not allow method overloading which is a different thing.

I like @Norman W. Freeman 's answer. That is the factory pattern and I use it regularly. %OnNew is a single method that is implemented in each class. There is a little known feature of %OnNew - it can return an oref whose type class is different from the class that implements %OnNew. In other words, %OnNew can be that factory method. Norman's Create() solution is cleaner IMO. The sole advantage (again, IMO) of a polymorphic %OnNew() is that it works with %New. I can provide a demo is anyone is interested.

Sorry - I just read Matt Gage's reply. My reply is redundant.

I am a huge fan of try/catch. The try is virtually cost-free with overhead only encountered when an exception is caught. I do agree with Jon Willeke - when I am in older code I normally do not refactor $ZTRAP unless the changes I have to make are more than a simple edit. I also use RETURN when exiting a function/method instead of QUIT.

It is possible to simluate a finally but it isn't as nice as having a real finally block would be.

And the first two lines can be combined:

set str = ##class(%Stream.FileCharacter).%OpenId("c:\myfolder\file.csv")