No - IDENTITY is for both Objects/SQL. I don't know why the $system.OBJ function was refactored to place it into a SQL class but IdentityInsert is a feature we added quite some time ago to support either T/SQL or Informix. 

IDENTITY property/column is system assigned but can be explicitly set and the IDKEY is based on this property. This is the global produced by my "E2" test where I set the id property to 100.

^com.intersystems.AppD=102
^com.intersystems.AppD(100)=$lb("","E2")

I don't know anything about ALLOWIDENTITYINSERT - maybe someone has added that since I was last involved with the compiler? 

But - as a general rule - you should not allow IDENTITYINSERT all of the time as it introduces a responsibility to keep the ID counter updated. When I first ran my test, it failed not because identity insert didn't work but because my counter wasn't updated to be 101 (I had inserted 100 with ID set).

UPDATE:

I researched this and found that while the documentation implies that you can define this parameter in any persistent class, that isn't the truth. The documentation appears to be wrong. It only works for classes using SQL Storage. It won't work for your case. Unless I misread the source code changes. I did look at %Persistent and that parameter is not defined by that class.

With a system-assigned %ID value (the IDKEY is defined as "system assigned" internally), you cannot override the value as there is no settable property. The way to work around that restriction is to define a property as the IDENTITY. It has similar behavior in that its value is system assigned but you can also override that assignment by specifying the value yourself. You have to set a switch that allows you to do that but it is possible. This type of behavior is similar to using sequences in PostgreSQL where some number of inserts that specify the id are followed by an update of the sequence used for id auto-assignment to ensure uniqueness later. Of course, when using IRIS and you specify the identity value, you need to be certain that a future increment (uses $sequence most likely) won't produce a value that will not be unique. 

This is a simple example of a class using identity.

class com.intersystems.App extends %Library.Persistent {

property id as %Library.BigInt [ identity ];
property name as %String;
classmethod add(name as %String) as %Integer {
    set me = ..%New()
    set me.name = name
    do me.%Save()
    return me.%Id()
}
}

I then implemented a simple unit test:

method TestAddWithId() {
    set obj = ##class(com.intersystems.App).%New()
    set obj.name = "E2"
    set obj.id = 100
    try {
        $$$ThrowOnError(obj.%Save())
        set id = obj.%Id()
        do $$$AssertEquals(100, id, "TestAdd() - add 'E2' with Id = 100")
    } catch exc {
        do $$$AssertTrue(0, "TestAddWithId() - Failed, exception caught: "_exc.AsSQLMessage())
    }
}

And the result of executing that test is:

The solution to this problem is to turn on IDENTITY_INSERT. I updated the test to do that.

method TestAddWithId() {
    set old = $system.SQL.SetIdentityInsert(1)
    try {
        do $system.SQL.Execute("delete from com_intersystems.App")
        set obj = ##class(com.intersystems.App).%New()
        set obj.name = "E2"
        set obj.id = 100
        $$$ThrowOnError(obj.%Save())
        set id = obj.%Id()
        do $$$AssertEquals(100, id, "TestAdd() - add 'E2' with Id = 100")
    } catch exc {
        do $$$AssertTrue(0, "TestAddWithId() - Failed, exception caught: "_exc.AsSQLMessage())
    }
    do $system.SQL.SetIdentityInsert(old)
}

and now the test passes:

Depending on what you really want to do, there are a couple of options. First of all, you can (and should perhaps) define a property as an IDENTITY. Setting that property directly requires certain privileges. 

If you don't really care about this property being used as the IDKEY, you can define a %Counter and define it as a Primary Key. It has been a while since I've used %Counter but I can come up with an example if this interests you.

Nice!

@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...