On Wed, May 31, 2017 at 12:53 PM, Michael Powell <[email protected]> wrote: > On Wed, May 31, 2017 at 12:09 AM, Alexander Zaytsev <[email protected]> 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. >> 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. > >> 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 >> >> -- >> >> --- >> 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 [email protected]. >> For more options, visit https://groups.google.com/d/optout. -- --- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
