CODEWITHSUNDEEP
jsonibm-integration-busextended-sql
Raghu
Raghu

Reputation: 21

JSON array creation in esql

I am new to IIB and I am struggling to create below JSON data in Extended SQL. Kindly suggest me.

{
    "name" : "John Doe",
    "age" : -1,
    "known" : false,
    "address" : { "street" : null, "city" : "unknown" },
    "belongings" : ["this", "that", "the other"]
}

My ESQL code:

DECLARE vm ROW;
    SET vm.Name[] = LIST{13,08,25,06};
    SET OutputRoot.JSON.Data.name = 'John Doe';
    SET OutputRoot.JSON.Data.age  =  1;
    SET OutputRoot.JSON.Data.known = false;
    SET OutputRoot.JSON.Data.address.street = null;
    SET OutputRoot.JSON.Data.address.city = 'Unknown';
    SET OutputRoot.JSON.Data.belongings = vm;

OUTPUT:

{
"name":"John Doe",
"age":1,
"known":false,
"address":{"city":"Unknown"},
"belongings":{"Name":13,"Name":8,"Name":25,"Name":6}
}

Upvotes: 2

Views: 15660

Answers (2)

Akshay Patil
Akshay Patil

Reputation: 11

The answer is correct but it would be better if you use a reference to optimize the performance of the code: -

DECLARE vm ROW;
DECLARE refBelong OutputRoot;
SET vm.Name[] = LIST{13,08,25,06};
SET OutputRoot.JSON.Data.name = 'John Doe';
SET OutputRoot.JSON.Data.age  =  1;
SET OutputRoot.JSON.Data.known = false;
SET OutputRoot.JSON.Data.address.street = null;
SET OutputRoot.JSON.Data.address.city = 'Unknown';
-- Keeping the above code as is

-- Creating a new field for belongings
CREATE LASTCHILD OF OutputRoot.JSON.Data AS refBelong NAME 'belongings';
-- This makes the belonging field an Array
SET refBelong.TYPE = JSON.Array;
-- The Item field is required to assign the value to JSON Array type
SET refBelong.Item[1] = 'this';
SET refBelong.Item[1] = 'that';
SET refBelong.Item[1] = 'this other';

Upvotes: 1

Daniele Chirivì
Daniele Chirivì

Reputation: 486

That's how you can create a JSON array:

CREATE FIELD OutputRoot.JSON.Data.belongings IDENTITY(JSON.Array)belongings;
SET OutputRoot.JSON.Data.belongings.Item[1]=13;
SET OutputRoot.JSON.Data.belongings.Item[2]=8;
SET OutputRoot.JSON.Data.belongings.Item[3]=25;
SET OutputRoot.JSON.Data.belongings.Item[4]=6;

Upvotes: 4

Related Questions