Question Re: How can I select a key from an JSON array in Technology Q&A

How can I select a key from an JSON array

PCollins — Sun, 07 Jul 2024 15:06:28 GMT

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.

Re: How can I select a key from an JSON array

fvestjens — Mon, 08 Jul 2024 04:02:21 GMT

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

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

Re: How can I select a key from an JSON array

VolkerBarth — Wed, 10 Jul 2024 10:56:34 GMT

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