JSON Support

[Alexander Zaytsev]

Hi,

I want to start talking about the JSON support for NHibernate. It seems this is a long-awaited feature, which, if properly implemented can give us some advantages over the other "shall not be named here" ORM.

I'll try to summarize the current state and what's needed to be done to implement this feature.

Current support of RDBMs engines

The current state of the RDBMS (not all of them, only the ones we care most about)

• Postgres 9.3+ - Supports json and jsonb column types. 
  
• Microsoft SQL Server 2016 - Supports some JSON functions over an NVARCHAR columns 
  
• Oracle 12c - Supports some JSON functions over VARCHAR2, CLOB, and BLOB columns
  
• SQLite - Has a loadable JSON extension
  
• MySQL 5.7+ - Supports some JSON functions over JSON column type
  
• Firebird - no support.
  

Persisting/Loading

The challenge here is that different RDBMS use different approaches how they store the JSON data. Also, as Microsoft has decided to store JSON as a string it's most likely that there will be no DbType.Json added to the ADO.NET.

There are several sensible ways to represent json data on the client side:

• as a String
  
• as a POCO object
  
• as a JObject (JSON.NET) or JsonObject (System.Json)
  

Querying RDBMS

It seems that there are 2 main approaches how the RDBMS support querying the JSON data:

• Arrow (Postgres, MySQL) or dot notations (Oracle)
  
• A single function to extract part of the json data: json_extract (MySQL, SQLite) or json_extract_path (Postgres)
  
• Two functions to extract part of the json data: json_value (to return a scalar value) and json_query (to return object or array). This is supported by SQL Server & Oracle
  SQL Server will return NULL or throw an error (depending on a server configuration) if the incorrect function is used.

For [2] and [3] functions the first operand is a JSON expression and the second argument is a JSON path expression as defined here http://goessner.net/articles/JsonPath/. 

It seems as the first phase it would be sensible to provide JSON querying support only for HQL and Linq. Criteria & QueryOver can be implemented as a second phase (if at all).

Dialects: because of [3] we will need to register 2 JSON querying functions which will be mapped to a single function for Postgres, MySQL and SQLite. 

HQL: We will not support the arrow or dot notations as it will require intensive AST tree rewrites. 

LINQ: 

Because of the peculiarity of SQL Server (json_query vs json_value), we will need to predict the intention of the user what they want to query: a part of a JSON or a scalar value. It seems to be easy in case of POCO classes, but can be tricky with String/JToken. To parse the Linq-2-JSON expression we will need to identify a root object of the query, and then build a JSON expression from this root object. Most likely we will need to do some analysis before the Re-Linq to prevent expansion of a QueryModel in case of JSON expression.

Some LINQ examples:

Given the Entity with a Data property storing following JSON object (from http://jsonpath.com/):

{

  "firstName": "John",

  "lastName" : "doe",

  "age"      : 26,

  "address"  : {

    "streetAddress": "naist street",

    "city"         : "Nara",

    "postalCode"   : "630-0192"

  },

  "phoneNumbers": [

    {

      "type"  : "iPhone",

      "number": "0123-4567-8888"

    },

    {

      "type"  : "home",

      "number": "0123-4567-8910"

    }

  ]

}

using (var session = OpenSession())

using (session.BeginTransaction())

{

var entities = (

from e in session.Query<Entity>()

where (string) JObject.Parse(e.Data)["firstName"] == "John" // This translates to WHERE JSON_VALUE(e.Data, "$.firstName") = "John"

select e).ToList();

}

using (var session = OpenSession())

using (session.BeginTransaction())

{

var entities = (

from e in session.Query<Entity>()

where (string) JObject.Parse(e.Data)["address"]["city"] == "Nara" // This translates to WHERE JSON_VALUE(e.Data, "$.address.city") = "Nara"

select e).ToList();

}

using (var session = OpenSession())

using (session.BeginTransaction())

{

var entities = (

from e in session.Query<Entity>()

select JObject.Parse(e.Data)["phoneNumbers"].Select(x => x["type"])).ToList(); // This translates to SELECT JSON_QUERY(e.Data, "$.phoneNumbers[*].type")

}

All suggestions and comments are welcome.

Best Regards,

Alexander

[Michael Powell]

On Wed, May 31, 2017 at 12:09 AM, Alexander Zaytsev <haz...@gmail.com> wrote:
> Hi,
>
> I want to start talking about the JSON support for NHibernate. It seems this
> is a long-awaited feature, which, if properly implemented can give us some
> advantages over the other "shall not be named here" ORM.

Well, the first question is, what is NHibernate accomplishing by this,
or the other "shall not be named here" ORM?

> I'll try to summarize the current state and what's needed to be done to
> implement this feature.
>
> Current support of RDBMs engines
>
> The current state of the RDBMS (not all of them, only the ones we care most
> about)
>
> Postgres 9.3+ - Supports json and jsonb column types.
> Microsoft SQL Server 2016 - Supports some JSON functions over an NVARCHAR
> columns

What kind of support do you expect for 2012? 2014?

> Oracle 12c - Supports some JSON functions over VARCHAR2, CLOB, and BLOB
> columns
> SQLite - Has a loadable JSON extension
> MySQL 5.7+ - Supports some JSON functions over JSON column type

Otherwise, any migration path at my disposal at the moment may lend
itself to MySQL paths.

> Firebird - no support.
>
>
> Persisting/Loading
>
> The challenge here is that different RDBMS use different approaches how they
> store the JSON data. Also, as Microsoft has decided to store JSON as a
> string it's most likely that there will be no DbType.Json added to the
> ADO.NET.
>
> There are several sensible ways to represent json data on the client side:
>
> as a String
> as a POCO object
> as a JObject (JSON.NET) or JsonObject (System.Json)

I'm not sure I would accept a tertiary dependency. But I'm also not
sure what a System.Json would incur.

A POCO object?

More likely, just support a String, and leave it to client side and/or
domain design time decisions, IMO.

> Querying RDBMS
>
> It seems that there are 2 main approaches how the RDBMS support querying the
> JSON data:
>
> Arrow (Postgres, MySQL) or dot notations (Oracle)
> A single function to extract part of the json data: json_extract (MySQL,
> SQLite) or json_extract_path (Postgres)
> Two functions to extract part of the json data: json_value (to return a
> scalar value) and json_query (to return object or array). This is supported
> by SQL Server & Oracle
> SQL Server will return NULL or throw an error (depending on a server
> configuration) if the incorrect function is used.

Which begins to explain what sort of dependency are we talking about
here. I wouldn't expect that JSON support would be native, which gets
challenging no matter how you slice it.

> --
>
> ---
> You received this message because you are subscribed to the Google Groups
> "nhibernate-development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to nhibernate-develo...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Michael Powell]

On Wed, May 31, 2017 at 12:53 PM, Michael Powell <mwpow...@gmail.com> wrote:
> On Wed, May 31, 2017 at 12:09 AM, Alexander Zaytsev <haz...@gmail.com> wrote:
>> Hi,
>>
>> I want to start talking about the JSON support for NHibernate. It seems this
>> is a long-awaited feature, which, if properly implemented can give us some
>> advantages over the other "shall not be named here" ORM.
>
> Well, the first question is, what is NHibernate accomplishing by this,
> or the other "shall not be named here" ORM?

Another consideration, as long as I can opt in, that I'm not latched
to external, especially .NET package dependencies.

[Michael Powell]

On Wed, May 31, 2017 at 12:09 AM, Alexander Zaytsev <haz...@gmail.com> wrote:

> Hi,
>
> I want to start talking about the JSON support for NHibernate. It seems this
> is a long-awaited feature, which, if properly implemented can give us some
> advantages over the other "shall not be named here" ORM.
>
> I'll try to summarize the current state and what's needed to be done to
> implement this feature.
>
> Current support of RDBMs engines
>
> The current state of the RDBMS (not all of them, only the ones we care most
> about)
>
> Postgres 9.3+ - Supports json and jsonb column types.
> Microsoft SQL Server 2016 - Supports some JSON functions over an NVARCHAR
> columns

One other important thought: remember that 2016 is Windows 10+ only.
Probably your SQL Server is being hosted on a current Windows Server
version. Probably. But for some developers, myself anyway, I am still
running on Windows 7. No plans to upgrade in the near future unless
something changes economically speaking.

[Frédéric Delaporte]

NHibernate Teamcity build agent is currently a Windows Server 2012 R2. Are you meaning Sql Server 2016 can only run on Windows 10/Server 2016? There is already a SQL server 2016 on that server.

According to https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server, it runs under Windows 8 and Server 2012. So not on a Windows 7, but not only Windows10+. Indeed they have released it for all OS still in mainstream support (8/2012+), but not for those in extended support (7/2008, ending in 2020).

Anyway, that should not prevent adding feature targeting a server not testable by some developers: NHibernate test suit can be configured by each developer to target what they have, and then it will ignore incompatible feature tests.

About supporting JSON querying with an API abstracting away databases specificity, of course it would be great. But since each db seem to go its own path without much standardization, it may require too much effort. I see that a bit like XML support, NHibernate do not provide a way to issue "for xml" queries likely because this has to much discrepancies from a database to another.

Now for JSON that seems way more simpler than XML, so why not trying anyway.

For me, there are two distinct areas to tackle:

• How NHibernate map a Json data on an entity?
  I think NHibernate should let the user map it as a plain string if he wants to. This may requires a JsonAsStringType for database defining a column as Json, or adapting the string type for handling those Json type. (May some data provider already provide deserialized values? If yes, it would have to serialize them back...)
  And Nhibernate should provide a type for mapping it as a poco (which class would be specified along the mapping otherwise inferred from the entity property).
  Having some dynamic mapping option would be great to. Maybe through dictionaries, for avoiding introducing a dependency on Json.Net or the not yet released System.Json on the entities (I only find obsolete Silverlight references for it or a pre-release Microsoft package).
  Of course, depending on how the data provider handles Json (does it handle serialization/deserialization or does it handle only already serialized Json?), NHibernate will still need to use a Json deserializer/serializer, and preferably a fast one like Json.Net. But this could be done through some Json serialization/deserialization provider, allowing the user to customize this.
  Supporting the SQL Server way means here we would also support any database even if not officially supporting Json.
• How NHibernate query Json data directly in the db? Here explicit database support is of course mandatory. And maybe the discrepancies between db will cause some difficulties.
  Maybe should we first start by providing two basic HQL function taking the json path as a string and mapping to json_value/json_query (or the unique function for db having one for all). And supply two extensions for calling them with Linq.
  That would already allow a basic querying support. This suppose the json path has the same syntax for all databases. Otherwise we will have to choose one syntax and convert it for other databases.
  From there, maybe try if we can propose something more elaborated.
  

[Michael Powell]

On Thu, Jun 1, 2017 at 10:05 AM, Frédéric Delaporte
<frederic...@free.fr> wrote:
> NHibernate Teamcity build agent is currently a Windows Server 2012 R2. Are
> you meaning Sql Server 2016 can only run on Windows 10/Server 2016? There is
> already a SQL server 2016 on that server.
>
> According to
> https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server,
> it runs under Windows 8 and Server 2012. So not on a Windows 7, but not only
> Windows10+. Indeed they have released it for all OS still in mainstream
> support (8/2012+), but not for those in extended support (7/2008, ending in
> 2020).

Yes, thank you for clarifying. Windows 8/2012+. Point is, no upgrade
path for Windows 7 users, at least not today.

> Anyway, that should not prevent adding feature targeting a server not
> testable by some developers: NHibernate test suit can be configured by each
> developer to target what they have, and then it will ignore incompatible
> feature tests.
>
> About supporting JSON querying with an API abstracting away databases
> specificity, of course it would be great. But since each db seem to go its
> own path without much standardization, it may require too much effort. I see
> that a bit like XML support, NHibernate do not provide a way to issue "for
> xml" queries likely because this has to much discrepancies from a database
> to another.

Supporting an agnostic Xml/XPath scenario shouldn't be too terribly
difficult IMO. As you say, it's a pathing issue, among other Xml
nuances, attributes, elements, etc.

> Now for JSON that seems way more simpler than XML, so why not trying anyway.
>
> For me, there are two distinct areas to tackle:
>
> How NHibernate map a Json data on an entity?

I could be wrong, but it seems to me a "simple" {"property": "value"}
approach would be *too* simple. How would you capture type
information? That would be key, to have a richer mechanism, like
{"property": {"type": "System.Object", "value": "value"}}. Just for
example. Of course, then there is the matter of value conversion
to/from strings, ostensibly.