How can I select a key from an JSON array

[PCollins]

Hi,

Hopefully a simple one, but how can I select the key name from an JSON array?

For example, in the following JSON I want to return "IM429" in a select, this could be one a dozen or so IM codes and there is always just one in a message with children only.

{
    "MailboxId": "b5afd3fc-5adb-4c25-81f6-e3be1485a94e",
    "TransactionId": "aa3b7fb0-8605-4098-95ba-1f83afd0ad85",
    "Message": {
        "IM429": {
            "Declaration": {...

Update:

I've been able to achieve what I'm after by using exception handling and checking for SQLCODE -1595 similar to the suggestion here, repeated for each of the message types and using labels and GOTO to control all tests.

[VolkerBarth]

IMHO that's more of a nested JSON object than a JSON array...

FWIW, I currently learnt that OpenString() is sometimes easier to work with to read contents of JSON arrays as part of larger JSON objects than trying to use sp_parse_json() to extract array elements...

[fvestjens]

You could also use locate() function to get the key name.

keyname = substr("json",locate("json",'"Message":{')+12,5)