split database output in mule

Question

I'm having below database output, how do i split it.

 [{country=United States, accountID=4410, street=12345 Green Rd, state=Colorado, name=Dale Burgers, city=Boulder, postal=80305}, {country=United Kingdom, accountID=4411, street=capthorne Avenue, state=Harrow, name=Arjun, city=london, postal=ha29eb}, {country=usa, accountID=4412, street=elden st, state=va, name=Home Foods, city=herndon, postal=20170}, {country=usa, accountID=4413, street=herndon parkway, state=va, name=Home Foods 2, city=herndon, postal=20170}, {country=usa, accountID=4414, street=van barun st, state=va, name=home foods 3, city=herndon, postal=20170}, {country=United Kingdom, accountID=4415, street=355 E Capthorne Court, Alexandra Avenue, state=Harrow, name=Barkha , city=Haarow London, postal=ha29eb}, {country=Canada, accountID=4416, street=4130 Chromoly, state=BC, name=Spawn Cycles, city=Squamish, postal=L0L0L0}, {country=Canada, accountID=4417, street=1 Yonge Street, state=BC, name=Post Spawn, city=Vancouver, postal=L1L1L1}, {country=Canada, accountID=4418, street=1 Yonge Street, state=BC, name=Post Post Spawn, city=Vancouver, postal=L2L2L2}, {country=Canada, accountID=4419, street=1 Yonge Street, state=BC, name=Post Post Post Spawn, city=Vancouver, postal=L0L0L0}]

i tried adding JSON to Object Transformer forming List type and Collection splitter but only first record is being passed.

 {country=United States, accountID=4410, street=12345 Green Rd, state=Colorado, name=Dale Burgers, city=Boulder, postal=80305}

@Mahesh_Loya can you please have a look ? – Rahul Dec 09 '17 at 15:36 — Rahul, Dec 09 '17 at 15:36

score 0 · Answer 1 · answered Dec 11 '17 at 04:55

0

The easiest way around is to convert the json to a java object using json to object transformer and that should provide you a collection of objects. You can then loop on this collection using foreach processor in Mule.

Hope this help.

answered Dec 11 '17 at 04:55

Ajoy Gupta

1
1

yeah i was able to do this way.. but it was failing by using splitter component .. I'm not sure what mistake I have been doing.. – Rahul Dec 12 '17 at 00:24
I guess in that case you will need to post a sample snippet for testing the issue – Ajoy Gupta Dec 13 '17 at 11:33

score 0 · Answer 2 · answered Dec 27 '17 at 09:05

Database connector is returning values in java.util.LinkedList format, If you want to split the result as record by record then use For-each scope after the Database poll component. Please find below the sample snippet:

 <db:mysql-config name="MySQL_Configuration" host="hostname" port="3306" user="mule" password="welcome1" database="schema" doc:name="MySQL Configuration"/>
    <flow name="database-47729955Flow">
        <poll doc:name="Poll">
            <fixed-frequency-scheduler frequency="10" timeUnit="SECONDS"/>
            <db:select config-ref="MySQL_Configuration" doc:name="Database">
                <db:parameterized-query><![CDATA[SELECT * FROM schema.table_name where B2B_REQUEST_TYPE='Send';]]></db:parameterized-query>
            </db:select>
        </poll>
        <foreach doc:name="For Each">
            <logger message="#['\n Database record is:'+payload]" level="INFO" doc:name="Logger"/>
            <logger message="#['\nNext record']" level="INFO" doc:name="Logger"/>
        </foreach>
    </flow>

Regards,

Sreenivas B

score -1 · Answer 3 · answered Dec 09 '17 at 16:33

Database output should be List or Iterator only. But if you are getting JSON. You can achieve it in following ways

Using Dataweave which will return List-

%dw 1.0
%output application/java
---
payload

Using Dataweave MEL function which will return Iterator (SplitIterator) -

<set-payload value="#[split('payload','application/json')]" doc:name="Set Payload"/>

Using JSON to Object Transformer -

<json:json-to-object-transformer returnClass="java.util.List" doc:name="JSON to Object"/>

Hope this helps.

split database output in mule

3 Answers3