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