Mule - iterate through the JSON payload and update database

0
votes

I have called a REST webservice using Mule http request component. The webservice did responded with the JSON payload now I have to parse this json and update my local database.

How can I parse the JSON response? how can I iterate through the JSON and update my db?

Please advice

-Paresh

1
jsonparsingmule

1 Answers

0
votes

You can iterate JSON by using JSON-to-object transformers and then use bulk update. Something like.

<flow name="testdbFlow">
    <http:listener config-ref="HTTP_Listener_Configuration" path="/test" doc:name="HTTP"/>
    <set-payload value="{&quot;1234567890123456&quot;:&quot;Y&quot;,&quot;1234567890123457&quot;:&quot;Y&quot;}" mimeType="application/json" doc:name="Set Payload"/>
    <dw:transform-message doc:name="Transform Message">
        <dw:input-payload />
        <dw:set-payload><![CDATA[%dw 1.0
            %output application/json
            ---
            (payload mapObject {    
                x: {
                    key : $$,
                    value : $
                }
            }).*x]]>
        </dw:set-payload>
    </dw:transform-message>
    <json:json-to-object-transformer returnClass="java.util.List" doc:name="JSON to Object"/>
    <db:update config-ref="MySQL_Configuration" bulkMode="true" doc:name="Database">
        <db:dynamic-query><![CDATA[UPDATE cwg_ws_data SET SyncFlag = '#[payload.value]' WHERE IMEI = '#[payload.key]']]></db:dynamic-query>
    </db:update>
</flow>

Where input is {"1234567890123456":"Y","1234567890123457":"Y"}. Please refer this answer for more details.