Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.


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": {...


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.

asked 07 Jul, 15:06

pcollins's gravatar image

accept rate: 0%

edited 07 Jul, 17:29

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

(2 days ago) Volker Barth

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

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

permanent link

answered 08 Jul, 04:02

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 07 Jul, 15:06

question was seen: 103 times

last updated: 2 days ago