title: How do I pass JSON as a primitive PostgreSQL type to a function using Dapper?
body: <p>I have a PostgreSQL function that takes in a parameter of type <code>json</code>. Using Dapper, how do I execute a call that passes the object to the PostgreSQL function such that PostgreSQL recognizes the type as <code>json</code> instead of as <code>text</code>? </p>
  
  <h2>Example PostgreSQL Function that takes in json type</h2>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION testfuncthattakesinjson(heroes json)
      RETURNS SETOF characters 
      LANGUAGE 'sql'
  
      STABLE
      ROWS 100
  AS $BODY$
  
      SELECT  c.*
      FROM    characters c
      JOIN    json_array_elements(heroes) j
      ON      c.first_name = j-&gt;&gt;'first_name'
      AND     c.last_name = j-&gt;&gt;'last_name';
  
  $BODY$;
  </code></pre>
  
  <h2>Broken Example C# Integration Test</h2>
  
  <pre class="lang-cs prettyprint-override"><code>[Test]
  public void Query_CallFunctionThatTakesInJsonParameter_FunctionUsesJsonType()
  {
      using (var conn = new NpgsqlConnection(Db.GetConnectionStringToDatabase()))
      {
          var funcName = "testfuncthattakesinjson";
          var expect = CharacterTestData.Where(character =&gt; character.id &lt;= 3);
          var jsonObj = JArray.FromObject(CharacterTestData
              .Where(character =&gt; character.id &lt;= 3)
              .Select(character =&gt; new Hero(character))
              .ToList());
  
          SqlMapper.AddTypeHandler(new JArrayTypeHandler());
  
          // Act 
          var results = conn.Query&lt;Character&gt;(funcName, new
          {
              heroes = jsonObj
          }, commandType: CommandType.StoredProcedure);
  
          // Assert
          CollectionAssert.AreEquivalent(expect, results);
      }
  }
  </code></pre>
  
  <h2>Supporting JArrayTypeHandler</h2>
  
  <pre class="lang-cs prettyprint-override"><code>internal class JArrayTypeHandler : SqlMapper.TypeHandler&lt;JArray&gt;
  {
      public override JArray Parse(object value)
      {
          throw new NotImplementedException();
      }
  
      public override void SetValue(IDbDataParameter parameter, JArray value)
      {
          parameter.Value = value;
      }
  }
  </code></pre>
  
  <p>In this current iteration, I've added a <code>SqlMapper.TypeHandler</code>. (At the moment, I'm only concerned with passing the <code>JArray</code> <em>to</em> PostgreSQL, hence the <code>NotImplmentedException</code> for <code>Parse</code>.)</p>
  
  <p>With this example, I get the following exception:</p>
  
  <blockquote>
    <p><strong>System.NotSupportedException</strong>: 'The CLR array type Newtonsoft.Json.Linq.JArray isn't supported by Npgsql or your PostgreSQL. If you wish to map it to an  PostgreSQL composite type array you need to register it before usage, please refer to the documentation.'</p>
  </blockquote>
  
  <p>In past iterations, I've also tried things like using a <code>List&lt;Hero&gt;</code> type handler and letting that type handler deal with the Json conversion.</p>
  
  <p>I've also tried adding the <code>Npgsql.Json.NET</code> Nuget package extension for Npgsql and call <code>conn.TypeMapper.UseJsonNet()</code> in my test method, but that didn't seem to have any effect.</p>
  
  <p>And if I do anything to serialize the object to a JSON string, then I get a different error (below), which makes sense.</p>
  
  <blockquote>
    <p><strong>Npgsql.PostgresException</strong>: '42883: function testfuncthattakesinjson(heroes => text) does not exist'</p>
  </blockquote>
  
  <p>So, <strong>it is possible to use Dapper to pass a JSON object as a PostgreSQL primitive to a function?</strong></p>