Write a code in esql to print following structure- getting data from database

Question

The output I am expecting, is

{ "a":1, "b": "string", "c":2, "d": "string", "e": 3, "f":[ { "g":4, "h": "string" } ] }

The problem is it is not having a root element at starting and when I am trying to loop into code I am using for loop, but it is overriding values and if I use Item[count] for second iteration. Also, it is printing "Item" like this : Code:

SET resultSet.rec\[\] = PASSTHRU(sqlQuery);

            DECLARE itemCount INTEGER 1;
    
                FOR dataref AS resultSet.rec[] DO
                    DECLARE inRef REFERENCE TO resultSet.rec[itemCount];
                    SET OutputRoot.JSON.Data.Item[itemCount].a= inRef.a;   
                    SET OutputRoot.JSON.Data.Item[itemCount].b= inRef.b;   
                    SET OutputRoot.JSON.Data.Item[itemCount].c= inRef.c; 
                    SET OutputRoot.JSON.Data.Item[itemCount].d= inRef.d;
                    SET OutputRoot.JSON.Data.Item[itemCount].e= inRef.e; --year 5
                    CREATE FIELD OutputRoot.JSON.Data.f IDENTITY(JSON.Array)f; 
                    SET OutputRoot.JSON.Data.f.Item[itemCount].g= inRef.g;
                    SET OutputRoot.JSON.Data.f.Item[itemCount].h= inRef.h;                                    
                    SET itemCount = itemCount+1;
                END FOR;

Then I am getting this result:

{ "Item": { "a":1, "b": "string", "c":2, "d": "string", "e": 3 }, "f":[ { "g":4, "h": "string" } ] }

My new code working for 1 iteration but replacing(overriding) values for 2nd iteration:

SET resultSet.rec\[\] = PASSTHRU(sqlQuery);
DECLARE itemCount INTEGER 1;

                FOR dataref AS resultSet.rec[] DO
                    DECLARE inRef REFERENCE TO resultSet.rec[itemCount];
                    SET OutputRoot.JSON.Data.a= inRef.a;   
                    SET OutputRoot.JSON.Data.b= inRef.b;   
                    SET OutputRoot.JSON.Data.c= inRef.c; 
                    SET OutputRoot.JSON.Data.d= inRef.d;
                    SET OutputRoot.JSON.Data.e= inRef.e; --year 5
                    CREATE FIELD OutputRoot.JSON.Data.f IDENTITY(JSON.Array)f; 
                    SET OutputRoot.JSON.Data.f.Item.g= inRef.g;
                    SET OutputRoot.JSON.Data.f.Item.h= inRef.h;                                    
                    SET itemCount = itemCount+1;
                END FOR;

Answer 1

If I understand you correctly you want a JSON array as result.

You can do it like this:

    CREATE FIELD OutputRoot.JSON.Data IDENTITY(JSON.Array)Data;
    DECLARE outRef REFERENCE TO OutputRoot;
    FOR inRef AS resultSet.rec[] DO
        CREATE LASTCHILD OF OutputRoot.JSON.Data AS outRef NAME 'Item';
        SET outRef.a = inRef.a;
        SET outRef.b = inRef.b;
        SET outRef.c = inRef.c;
        SET outRef.d = inRef.d;
        SET outRef.e = inRef.e;
        CREATE FIELD outRef.f IDENTITY(JSON.Array)f;
        SET outRef.f.Item.g= inRef.g;
        SET outRef.f.Item.h= inRef.h;
    END FOR;

This will produce following JSON response:

[
  {
    "a": 1,
    "b": "string",
    "c": 2,
    "d": "string",
    "e": 3,
    "f": [
      {
        "g": 4,
        "h": "string"
      }
    ]
  },
  {
    "a": 5,
    "b": "string",
    "c": 6,
    "d": "string",
    "e": 7,
    "f": [
      {
        "g": 8,
        "h": "string"
      }
    ]
  }
]

Use CREATE LASTCHILD to avoid array subscripts [] navigation:

Array subscripts [ ] are expensive in terms of performance

See ESQL array processing.