Native JSON support

[pawelpabich]

Some database (such as SQL Server) allow you to run queries that reason about JSON data in a column. It would be good to enable query to be able to make use of this, i.e. be able to sort/filter/etc. by values that part of a JSON column.

When looking at this we should also consider enabling taking that JSON data from a single column and deserializing it into multiple properties.

---

Original Contents

Adding some more general comments for the scenario this is tracking, but keeping the original contents of the issue

http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx

What are your current thoughts about that ?

[CoreyKaylor]

Hoping that there is some ability to query against the json document similar to how postgresql can do already today. Even better is if the ability to query can be done from EF through Linq.

[rowanmiller]

We have been discussing this with the SQL Server team. I'm just waiting to hear back from them about how much detail of the feature I can discuss publically before answering this one 😄

[rowanmiller]

So JSON support is mostly about the FOR JSON key words which allow you to render a result set as JSON.

The issue is that when using this with EF the expectation would be that the JSON would be shaped after your domain model rather than the database schema. For example, if the [dbo].[products].[product_id] column is mapped to the Product.ProductId property then EF users would expect the JSON to use Product/ProductId rather than products/product_id. Of course for this simple scenario we could just use column aliasing in the query, however it gets more complicated when you start having relationships, tables split to multiple entity classes, tables combined into a single entity class, inheritance, etc. Bottom line is that I’m not sure how feasible it would be to actually use the FROM JSON syntax since the logic to do all this mapping etc. is handled by processing the query results.

[pawelpabich]

Wow, so no extensions to TSQL so we can query the data? That would be very disappointing. Would you be able to to point us to a doc/spec that describes JSON support in SQL 2016?

[ErikEJ]

@pawelpabich Be careful what you ask for - http://www.brentozar.com/archive/2015/05/reading-the-sql-server-2016-data-sheet/

[pawelpabich]

@ErikEJ PostgreSQL has done it and it seems to work well. I trust Microsoft engineers can do at least as good job.

[divega]

It seems se are talking about two separare things:

• The ability to return query resulta directly formatted as JSON from the server, which is what I know the upcoming 'FOR JSON' feature does and what Rowan mentions we have been discussing with the SQL Server team
• The ability to query inside JSON documents stored in SQL Server directly in SQL, which I don't know if it is coming but that I will ask about.

[divega]

Regarding the ability to return results as JSON in particular, another potential mismatch between the SQL Server feature and EF7 is the fact that the latter does not guarantee that a LINQ query will be translated into a single SQL query, e.g. currently if the 'Include()' method is used with collection navigation properties, EF7 can translate into multiple SQL queries. An hypothetical 'ForJson()' extension method would then need to concatenate multiple results in a way that made sense.

[JocaPC]

In SQL Server we will represent JSON as plain string. I don't believe that native JSON type will be added in 2016 version. However, we will have built-in functions that find values in JSON string using some JavaScript-like path. As an example, in PostgreSQL you can use #>> operator to find a value on the path "a[2]":

SELECT '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

We will have JSON_VALUE built-in function instead of #>> operator and more JavaScript friendly path syntax:

SELECT JSON_VALUE('{"a":[1,2,3],"b":[4,5,6]}', '$.a[2]')

I'm not sure how you would like to query JSON via EF. One option might be to represent some string column that contains JSON (e.g. jsonProperty) as a dynamic property in EF and reference some "virtual field" in that dynamic property (e.g. entity.jsonProperty.info.address[2].town). In this case maybe we might transform this expression to something like:

JSON_VALUE(entity.jsonProperty, '$.info.address[2].town')

Is this something that you will like to see in EF?

[pawelpabich]

@JocaPC thanks for your input. I would expect to have a single column in the database with the entity fully serialized there. I can imagine we could have some additional columns like primary key. When it comes to queries I would like to have implemented as many LINQ methods as possible.

Re SQL 2016 if it simply is a plain string then I can imagine there will be no indexes so all queries will be doing table scan ?

[CoreyKaylor]

Postgresql queries against jsonb structures are fully indexed. Wonder why that isn't possible with SQL Server as well. I can see this neutered behavior to exist as a stepping stone, but as is it will have little value to me.

[JocaPC]

You will be able to create computed columns that reference some value in JSON and then create regular B-tree index on that computed column. Once we integrate JSON and Hekaton you will be able to use in-memory hash indexes. Maybe we will add JSON filter to FULL TEXT SEARCH indexes in future. Query optimizer will use the index if the referenced value is used in some query and seek into the table. If this is not enough we will implement the type - it is just a question of what functionality will come first (we are focusing on built in functions).

PostgreSQL/Oracle do not have some customized JSON indexes. They are also extracting properties from JSON that are used in regular indexes as scalar values, e.g. CREATE INDEX ON publishers((info->>'name')) similar to the indexes on computed columns, or use gin index on some property/document (see http://www.postgresql.org/docs/9.4/static/datatype-json.html#JSON-INDEXING).

I don't believe that this discussion is related to EF so maybe you can ask this kind of questions on stackoverflow :)

[CoreyKaylor]

It started out that way. :) I think it's relevant to discuss and compare what's possible against any target that EF would potentially support.

[divega]

@JocaPC thanks a lot for explaining. I actually believe that in the long run this aspect of JSON support may be a better fit for EF usage than the 'FOR JSON' feature.

Think about aggregates made of entities and/or complex types that instead of being shreaded into it's individual scalar properties before they can be persisted are simply serialized as JSON and persisted as a large string column. Also think about enabling the translation of a path reference into that graph from LINQ to the corresponding 'JSON_VALUE()' expression, plus the ability to optimize specific paths through indexing.

Sounds a lot like a hybrid of relational and document databases that could have compelling characteristics.

[pawelpabich]

@JocaPC the details you are providing are valuable as they determine what can and can not be done in EF. From what you are saying it looks there will be enough JSON support in SQL 2016 to make it a great addition to EF.

@divega great summary

[sgentile]

It's 2015 and MS still only supports MSSQL - I thought this was open source ? Rails supports Postgres, my Node app supports Postgres, Phoenix/Elixir supports Postgres, etc... Then on top of that, they all support JsonB. How many years has EF been around and it still can't do the basic of things ? Open source means more than showing us the code, it means making it able to run on platforms and services outside of just Microsoft.

[roji]

One comment on the PostgreSQL side of things... I'm planning to look at what can be done to expose PostgreSQL JSON/JSONB operations in Npgsql's EF7 provider. For example, it should be possible to translate some calls to Newtonsoft JSON.NET's search APIs into PG operators. I've done some similar work on translating .NET the regex API to PostgreSQL's regex operations, although JSON should be a more complex task.

[ErikEJ]

@sgentile EF7 has providers for Postgress, SQLite and others may be coming up later

[rowanmiller]

@sgentile – EF6 has providers for Postgres, Oracle, MySQL, DB2, SQLite, Firebird and many others. Hopefully we are doing better than “we are showing you the code now”… we have been collaborating with various folks to build an ecosystem of providers for EF7. We build some ourselves, and others are built by third parties. Our third party providers have also contributed code back into our core code base to fix issues they hit and better support their providers. We also take code contributions from other folks too.

[sgentile]

ok, well that is good. So perhaps the issue is more around the code generation I see happening with the yeoman aspnet generator - ie. in rails I say rails new --database=postgresql - it fetches the pg gem, and the .yml is setup for postgres - whereas with EF7 in dnx I am not sure the process here? (I'm developing in Linux)

[sgentile]

I see you mention EF6, I'm looking at ES7 while running DNX - This is what I see http://www.npgsql.org/doc/coreclr.html When you guys all say it's available, what I see is that it's mostly 'experimental', and not stable. I guess when I'm told it has providers and support, I'm not looking at just as a prototype, I'm looking at serious production apps.

[sgentile]

Let me apologize, as I realize this is all under development and not at a release status. And also, for highjacking this issue. The announcement of the latest Visual Studio Code led me to start creating a project, as we are starting a new project ourselves to run in Docker instance of Linux, and I was curious of the progress being that I haven't used EF is a few years. I'm very interested in what your teams are doing to provide true cross platform development. So the excite wanes when I realize I can certainly now build web solutions in .net on Linux/OSX, but I think the bottleneck is the availability of a full stack open source solution. Patience I know :)

[JocaPC]

Since JSON support in SQL Server has different functionalities we should be more specific and describe what could be integrated. Probably FOR JSON does not makes sense for EF integration, but some functions might be useful. I have proposed one change in #4021 so we can start discussion about this use case there.
If you have another ideas how EF/SQL Server integration might look you can create additional issues and describe scenario.

[divega]

@JocaPC sounds good. I think #4021 represent a good set of goals for JSON support in EF. I would prefer to close this issue (#2141) and use that one instead to track the feature in our backlog.

cc @rowanmiller

[rowanmiller]

Agreed. #4021 covers everything from we intended in this issue, and more.