NH-3930 - Support for mapping/quering JSON fields

ma3yta created an issue — 6th January 2017, 5:58:58:

Currently in .net world we don't have ORM which allow us to quering JSON fields from DB. In Entity Framework it is hard to implement regarding to wrong architecture.

Could you please add support for it?

Maybe this link helps:

https://www.linkedin.com/pulse/quering-json-using-hibernate-marvin-froeder http://jasperfx.github.io/marten/ https://github.com/jasperfx/marten

ma3yta added a comment — 6th January 2017, 6:00:52: https://github.com/aspnet/EntityFramework/issues/4021

Fabio Maulo added a comment — 12th January 2017, 20:49:03: In the core teh JsonType is needed
  [Serializable]
  public class JsonType<TSerializable> : MutableType
  {
      private readonly Type serializableClass;
      private readonly StringClobType dbType;

      public JsonType() : base(new StringClobSqlType())
      {
          serializableClass = typeof(TSerializable);
          dbType = NHibernateUtil.StringClob;
      }

      public override void Set(IDbCommand st, object value, int index)
      {
          dbType.Set(st, Serialize(value), index);
      }

      public override object Get(IDataReader rs, string name)
      {
          return Get(rs, rs.GetOrdinal(name));
      }

      public override object Get(IDataReader rs, int index)
      {
          string dbValue = (string)dbType.Get(rs, index);
          return string.IsNullOrEmpty(dbValue) ? null : Deserialize(dbValue);
      }

      public override Type ReturnedClass => serializableClass;

      public override bool IsEqual(object x, object y)
      {
          if (ReferenceEquals(x, y))
          {
              return true;
          }

          if (x == null | y == null)
|
          {
              return false;
          }

          return x.Equals(y) | dbType.IsEqual(Serialize(x), Serialize(y));
|
      }

      public override int GetHashCode(object x, EntityMode entityMode)
      {
          return dbType.GetHashCode(Serialize(x), entityMode);
      }

      public override string ToString(object value)
      {
          return Serialize(value);
      }

      public override object FromStringValue(string xml)
      {
          return Deserialize((string)dbType.FromStringValue(xml));
      }

      public static string Alias => string.Concat("json_", typeof(TSerializable).Name);

      public override string Name => Alias;

      public override object DeepCopyNotNull(object value)
      {
          return Deserialize(Serialize(value));
      }

      private string Serialize(object obj)
      {
          try
          {
              return JsonConvert.SerializeObject(obj);
          }
          catch (Exception e)
          {
              throw new SerializationException("Could not serialize a serializable property: ", e);
          }
      }

      public object Deserialize(string dbValue)
      {
          try
          {
              return JsonConvert.DeserializeObject(dbValue, serializableClass);
          }
          catch (Exception e)
          {
              throw new SerializationException("Could not deserialize a serializable property: ", e);
          }
      }

      public override object Assemble(object cached, ISessionImplementor session, object owner)
      {
          return (cached == null) ? null : Deserialize((string)cached);
      }

      public override object Disassemble(object value, ISessionImplementor session, object owner)
      {
          return (value == null) ? null : Serialize(value);
      }
  }
Then some dialects will need the Function to query inside a JSON field. For the LINQ provider the function can be added with something like this http://fabiomaulo.blogspot.com.ar/2010/07/nhibernate-linq-provider-extension.html the problem will be on how throw a nice exception when the dialect does not support the function to query inside a JSON... but well...perhaps an ugly exception can be accepted be the user.

Fabio Maulo added a comment — 12th January 2017, 20:54:51: Ah... I have forgot to mention that the biggest problem to put such thing directly in the core is the fact that just for a Type we have to add a reference to a real JSON serializer. Because NH is so injectable, perhaps a separated NuGet package would be better.

Jovan Popovic added a comment — 18th January 2017, 17:54:50: Besides generic JsonType, another important thing is mapping non-value types such as string<], int[>, or simple POCO objects to JSON columns. In many cases, we would need to create string[] Tags, Point location, and other properties that have types where we don't want to map to separate tables. Maybe this would be more common case since we can create Blog or Post with string[] Tags that should be stored in NVARCHAR column as ["C#",".NET","MVC"]. Here is article that explains how we can map these fields in EF Core: https://www.codeproject.com/Articles/1166099/Entity-Framework-Storing-complex-properties-as-JSO

nhibernate / nhibernate-core

NH-3930 - Support for mapping/quering JSON fields #957