title: How to read REST service that returns JSON object and store the same into PostgreSQL using Mule ESB, Mule Studio
body: <p>So I have a REST based service which is hosted at</p>
  
  <pre><code>http://localhost:35798/RestServiceImpl.svc/json/567
  </code></pre>
  
  <p>If I query that, I get the result as:</p>
  
  <pre><code>{"JSONDataResult":"You requested product 567"}
  </code></pre>
  
  <p>I need to store the whole JSON data into a PostgreSQL table:</p>
  
  <pre><code>CREATE TABLE "JsonTable"
  (
    "StoreJsonObject" json
  )
  </code></pre>
  
  <p>If I want to parse the value field i.e. "You requested product 567", the program works (here I am using a different table whose column type is text):</p>
  
  <pre><code>&lt;jdbc:postgresql-data-source name="PostgreSQL_Data_Source" user="username" password="pwd" url="jdbc:postgresql://localhost:5432/TestDB" transactionIsolation="UNSPECIFIED" doc:name="PostgreSQL Data Source"/&gt;
  &lt;jdbc:connector name="PostgreSQL_Connector" dataSource-ref="PostgreSQL_Data_Source" validateConnections="true" queryTimeout="-1" pollingFrequency="0" doc:name="Database"&gt;
      &lt;jdbc:query key="InsertRecord" value="INSERT INTO &amp;quot;AnotherJSonTable&amp;quot;(&amp;quot;StoreJsonObject&amp;quot;) VALUES (#[message.payload])"/&gt;
  &lt;/jdbc:connector&gt;
  &lt;flow name="testRestFlow1" doc:name="testRestFlow1"&gt;
      &lt;http:inbound-endpoint exchange-pattern="request-response" address="http://localhost:8082/index.html"  doc:name="HTTP"/&gt;
      &lt;http:rest-service-component httpMethod="GET" serviceUrl="http://localhost:35798/RestServiceImpl.svc/json/567"&gt;
      &lt;/http:rest-service-component&gt;
      &lt;json:json-to-object-transformer returnClass="java.util.Map" doc:name="JSON to Object"/&gt;
      &lt;expression-transformer expression="#[message.payload.JSONDataResult]" doc:name="Expression"/&gt;
      &lt;jdbc:outbound-endpoint exchange-pattern="one-way" queryKey="InsertRecord" queryTimeout="-1" connector-ref="PostgreSQL_Connector" doc:name="Database"/&gt;
  &lt;/flow&gt;
  </code></pre>
  
  <p>But how to store the entire JSON object ({"JSONDataResult":"You requested product 567"}).<br>
  What do I need to change in the "expression-transformer"?</p>
  
  <p>If I do:</p>
  
  <pre><code>&lt;jdbc:query key="InsertRecord" value="INSERT INTO &amp;quot;JsonTable&amp;quot;(&amp;quot;StoreJsonObject&amp;quot;) VALUES (#[message.payload])"/&gt;
  
  &lt;expression-transformer expression="#[message.payload]" doc:name="Expression"/&gt;
  </code></pre>
  
  <p>I receive exception:</p>
  
  <pre><code>Root Exception stack trace:
  org.postgresql.util.PSQLException: No hstore extension installed.
      at org.postgresql.jdbc2.AbstractJdbc2Statement.setMap(AbstractJdbc2Statement.java:1713)
      at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1916)
      at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:36)
      + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
  ********************************************************************************
  
  INFO  2012-12-24 15:48:31,945 [[testrest].connector.file.mule.default.receiver.01] org.mule.transport.file.FileMessageReceiver: Lock obtained on file: C:\Users\niladri.biswas\Desktop\input\all_winjs_ui_controls.txt
  INFO  2012-12-24 15:48:31,945 [[testrest].testRestFlow1.stage1.02] org.mule.transport.http.components.RestServiceWrapper: Invoking REST service: http://localhost:35798/RestServiceImpl.svc/json/567
  ERROR 2012-12-24 15:48:31,992 [[testrest].PostgreSQL_Connector.dispatcher.01] org.mule.exception.DefaultMessagingExceptionStrategy: 
  ********************************************************************************
  Message               : Failed to route event via endpoint: DefaultOutboundEndpoint{endpointUri=jdbc://InsertRecord, connector=JdbcConnector
  {
    name=PostgreSQL_Connector
    lifecycle=start
    this=15e7ea6
    numberOfConcurrentTransactedReceivers=4
    createMultipleTransactedReceivers=false
    connected=true
    supportedProtocols=[jdbc]
    serviceOverrides=&lt;none&gt;
  }
  ,  name='endpoint.jdbc.InsertRecord', mep=ONE_WAY, properties={queryTimeout=-1}, transactionConfig=Transaction{factory=null, action=INDIFFERENT, timeout=0}, deleteUnacceptedMessages=false, initialState=started, responseTimeout=10000, endpointEncoding=UTF-8, disableTransportTransformer=false}. Message payload is of type: LinkedHashMap
  Code                  : MULE_ERROR--2
  --------------------------------------------------------------------------------
  </code></pre>
  
  <p>Also the record should be inserted only once after reading from the service and not multiple times.</p>