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? > 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.
