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. > 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 > > -- > > --- > 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-development+unsubscr...@googlegroups.com. > 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 nhibernate-development+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
