[SOLVED] How do I access JSON child node data in ESQL?

How do I access JSON child node data in ESQL?

Many thanks for looking at this for me.

I am working in IBM ACE V11 software and in my service, I receive a JSON message.

I need to map this JSON message to a SOAP request via ESQL.

Please see the sample message below:

Incoming JSON message:

   "journals": [
                    {
                            "journalName": "Plant Species in London",
                            "journalYear": "2016",
                            "journalAuthor": [
                                    {
                                            "name": "Julian Bose",
                                            "subject": "botany"
                                    }
                                    {
                                            "name": "Samantha Adams",
                                            "subject": "biology"
                                    }, 
                            ],
                            "samplePolling": {
                                    "pollingInterval": 120,
                                    "totalAttempts": 10
                            }
                    },
              
            ],
            "supervisorName": "James Smith"
    }

In ESQL I have so far:

For Journal's Name:

SET OutputRoot.SOAP.Body.ns:submitJournal.ns:journalName = InputRoot.JSON.Data.journals.journalName;

For Journal's Year:

SET OutputRoot.SOAP.Body.ns:submitJournal.ns:journalYear = InputRoot.JSON.Data.journals.journalYear;

For Journal's Author, I have a problem. The problem is that there can be 0 to 3 or more authors. In this case, there are 2 authors.

How do I first check if any authors are present and if so, how many are there and then how to assign each authors' details to SOAP. (All of this in ESQL).

In ESQL I have this so far. But I don't know how to get the "n" value. (n represents no. of authors).

SET OutputRoot.SOAP.Body.ns:submitJournal.ns:journalAuthorValues[n].ns16:AuthorName = InputRoot.JSON.journals.journalAuthor[n].name;

Any and all help is greatly appreciated.

Solution

For Journal's Author, I have a problem. The problem is that there can be 0 to 3 or more authors. In this case, there are 2 authors.

You need to iterate over the array of authors, and you are assuming that you need to count the number of authors. But you do not need to. This should work just fine (not tested, may contain syntax errors)

FOR refAuthor AS InputRoot.JSON.Data.journals.(JSON.Array)journalAuthor[] DO
    CREATE LASTCHILD OF OutputRoot.SOAP.Body.ns:submitJournal.ns:journalAuthorValues 
       TYPE NAMEVALUE
       IDENTITY ns16:AuthorName
       VALUE FIELDVALUE(refAuthor);
END FOR

You should try to avoid using counted loops in ESQL. A FOR statement or a SELECT statement is almost always simpler and better.