Map JSON values stored in database to EF properties

[JocaPC]

SQL Server, PostgreSQL, MySQL, and Oracle databases enable developers to store JSON in columns and use values from JSON documents in queries. As an example, we can create product table with few fixed columns and variable information stored as JSON:

Product[id,title,decription,datecreated,info]

info column can contain JSON text. Use cases are:

1. I can put different non-standard information about products in JSON column as a set of key:value pairs in JSON column, e.g. {"color":"red","price":35.99,"status":1}.
2. I can have new kind of single table inheritance where I can put all properties specific to some leaf classes as a bag of key:values instead of additional columns.

In SQL Server/Oracle we can use JSON_VALUE function to read JSON values from JSN column by keys, e.g.:

SELECT id, title,
       json_value(info,'$.price'),json_value(info,'$.color'),json_value(info,'$.status')
FROM product

PostgreSQL has ->> operator and MySQL has json_extract function that are similar.

Problem

Currently, JSON fields can be mapped to entities in entity framework as string properties and we can parse them using Json.Net library. It would be good if we could map properties in EF model to (JSON text+path).

Proposal

1. Basic - Could we map property from EF model to string column that contains text and specify path of value in JSON (e.g. price, status) ? EF can extract json value on JSON path and populate property in EF model with proper type casting.
2. Medium - Enable updates of properties mapped to json value. If some EF property that is mapped to JSON values is updated, EF could format all of them as JSON and save them back in the in JSON column.
3. Advanced - Enable LINQ support over JSON properties. If we use Select(obj=>obj.price), or Where(obj => obj.price < 100) in LINQ queries, these predicates could be transformed to JSON_VALUE for SQL Server/Oracle, -> for PostgreSQL. This way we will be able to query JSON values directly from EF.

edited by @smitpatel on 16th March 2018

You can use JSON_VALUE function using user defined function feature in EF Core 2.0 https://github.com/aspnet/EntityFrameworkCore/issues/11295#issuecomment-373852015 explains how to do it for SqlServer.

Some open questions/possible subtasks:

• Partial updates to JSON documents (would likely require modeling as owned entities)
• Allow configuring JsonSerializationOptions to customize JSON property name etc., on a property-by-property basis (but also allow some bulk configuration default?)
• Use or allow using the new System.Text.Json source generator introduced in .NET 6.0?
• Indexes should probably out of scope of general relational support, as support differs considerably across databases.

Issues

• [ ] #27779

[ramax495]

Any development on this feature? It's very usefull!

[ajcvickers]

@ramax495 This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[CoskunSunali]

@mutanttech the solution provided in #11295 is legitimate and reliable. The query translates to JSON_VALUE correctly. It's just unfortunately not bundled with EFCore.

@aaronhudon For selecting primitive values, yes. For selecting real complex JSON data that can exceed 4000 chars, no. You have to switch to OPENJSON in that case and it has a whole another syntax.

[CoskunSunali]

@CoskunSunali - Thanks for bringing it to attention. Though I believe, it would not be difficult to define function based on OPENJSON similarly.

@smitpatel Sorry for the late response. I am not sure it would be that easy for average Joe. OPENJSON has a whole another syntax that requires common table expressions like WITH or a CROSS APPLY or even both at the same time.

Example (taken from here):

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street varchar(500) ,  postcode  varchar(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Comparing it to JSON_VALUE, it is quite a complex statement for querying data. I mean, from expression point of view, not when you write raw T-SQL. Consider partial selects, partial updates, array values and so on.

[aaronhudon]

@mutanttech the solution provided in #11295 is legitimate and reliable. The query translates to JSON_VALUE correctly. It's just unfortunately not bundled with EFCore.

@aaronhudon For selecting primitive values, yes. For selecting real complex JSON data that can exceed 4000 chars, no. You have to switch to OPENJSON in that case and it has a whole another syntax.

@mutanttech the solution provided in #11295 is legitimate and reliable. The query translates to JSON_VALUE correctly. It's just unfortunately not bundled with EFCore.

@aaronhudon For selecting primitive values, yes. For selecting real complex JSON data that can exceed 4000 chars, no. You have to switch to OPENJSON in that case and it has a whole another syntax.

@CoskunSunali for "complex" JSON fragments, use the same technique as described for JSON_VALUE, but use JSON_QUERY, then materialize the result by deserializing into a client side model. I've been doing this, and it works pretty well.

[weitzhandler]

Time to bring this into SQL Server provider. https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/pull/982 Hooray @roji!

[CADbloke]

If you're looking for a workaround...

• for .NET framework: https://stackoverflow.com/questions/20711986/entity-framework-code-first-cant-store-liststring/37207034#37207034 can be bodged to fit
• for .NET core: https://stackoverflow.com/questions/20711986/entity-framework-code-first-cant-store-liststring/56727929#56727929 is quite elegant.

[weitzhandler]

@CADbloke I think you misunderstood me. Npgsql has support for mapping those JSON objects to POCO classes, and even enables querying them in the DB. Otherwise, I misunderstood what that PR was about.

[roji]

The Npgsql provider version 3.0 indeed added out the ability to map properties of EF entities to database JSON columns, and to query within those columns with LINQ as usual.

@CADbloke's links above do provide alternative means to do the mapping - a value converter can be used to convert any user POCO type to a JSON document (via Json.NET or System.Text.Json) to be sent to the database. However, that approach does not allow querying, only storage.

[weitzhandler]

I knew I didn't underestimate your power @roji...

[markusschaber]

@CADbloke In the linked case, npgsql can even map a .NET List<string> or string[] into a PostgreSQL column of type text[], no need of JSON at all: https://www.npgsql.org/efcore/mapping/array.html

This can even be combined with costom conversions, we're successfully mapping sets of our own structs to PostgreSQL text[] by using a ValueConverter<SortedSet<OurStruct>,string[]>. (The sets are empty in most cases, and only contain half a handful of entries otherwise, so an extra table was not worth the hassle...)

Never underestimate the Power of PostgreSQL and npgsql :-)

PS: We've been motivated by Andrew Locks article series about primitive obsession: https://andrewlock.net/generating-strongly-typed-ids-at-build-time-with-roslyn-using-strongly-typed-entity-ids-to-avoid-primitive-obsession-part-5/

[rogerfar]

@roji any plans to bring this over to the SQL Server provider?

[roji]

@rogerfar nothing concrete at the moment - but we haven't yet done the planning for our next version (after 3.1), this might make it in.

[sishuoyang]

I can't believe this issue is still open in 2020! This feature is highly desirable.

[marchy]

As soon as EF Core becomes a viable alternative to EF 6.x – and can support real production features such as TPT (open since 2015 – https://github.com/dotnet/efcore/issues/2266), I'm sure the team will begin planning this one out and "consider it".

[mguinness]

Please thumbs up the original post so that it has a better chance of being considered in the Plan for Entity Framework Core 5.0.

Your feedback on planning is important. The best way to indicate the importance of an issue is to vote (thumbs-up 👍) for that issue on GitHub. This data will then feed into the planning process for the next release.

[aaronhudon]

Please thumbs up the original post so that it has a better chance of being considered in the Plan for Entity Framework Core 5.0.

Your feedback on planning is important. The best way to indicate the importance of an issue is to vote (thumbs-up 👍) for that issue on GitHub. This data will then feed into the planning process for the next release.

@mguinness FYI, if you're working on a new project, npgsql has JSON licked like a hot damn, I'm very happy with it so far. https://www.npgsql.org/efcore/mapping/json.html?tabs=data-annotations%2Cpoco

[marchy]

Guess it's (finally?) time to switch over to PostgreSQL.

SQLServer has been the last thorn in our side with .NET Core / multi-platform .NET development (ie: not needing to spin up a Windows VM just to be able to access SQLServer/SSMS), and the magnitude + importance of this missing feature might just be the motivation to go all-in on the DB switch.

Thanks @roji for illuminating us on the fantastic benefits of the Npgsql, including the NodaTime support (we've been battling with DateTime hacks for years), primitive arrays, native enums etc. Looks like the provider is WAY ahead of the SQLServer one!

[roji]

not needing to spin up a Windows VM just to be able to access SQLServer/SSMS

To be fair, SQL Server seems to work great on Linux, so if Windows is a big issue you may want to consider that.

As for the rest, a lot of the cool stuff (arrays, enums) is things PostgreSQL itself supports - the provider only surfaces that (just to make it clear where the goodness is coming from).

[atrauzzi]

PostgreSQL > MSSQL for licensing alone. Even if that's not an immediate concern, take it seriously on principle.

I can't even begin to count the tricky ways you can get cornered by MSSQL and duped into an upsell.

[marchy]

Thanks for the thoughts guys.

We went with the typical least-resistance path in choosing MSSQL (out of not knowing better, since we're devs not DBAs), in hope for maximum compatibility with the upwards stack (EF6/EFCore/.NET).

Ironically that path seems to have NOT optimized for maximum support/compatibility – and when database features such as the lack of native enums, arrays and JSON columns don't exist in the DB/provider, they end up inherently limiting and skewing your higher-level abstractions you are able to leverage, thus getting less value upwards on the stack – double irony.

Need no further convincing over here.

[marchy]

To be fair, SQL Server seems to work great on Linux, so if Windows is a big issue you may want to consider that.

If I may ask what DB tool is good in replacement of SSMS on Mac (preferrably) Linux? It's more the tooling that has held us back (make backups/restores easily during dev process) than anything.

Tried running DataGrid on Docker instances but were unable to get the connection working successfully, and did not find any support for .bak file backup/restore (ie: .bak files rather than SQL-based import/export)

Apologies for the slighty off-topic skew of the discussion – but strangely enough all these decisions go together and boil down to the adoption of JSON-mapped fields in the ORM.

[ErikEJ]

Azure Data Studio with extensions

[marchy]

Fantastic @ErikEJ Thanks!

[ErikEJ]

@marchy Thanks for the positive feedback!

[Eli-Black-Work]

Hello :)

Would you also consider adding support for modifying JSON in the DB without first retrieving the full JSON?

For example, T-SQL supports "in situ" modification of JSON via the JSON_MODIFY() T-SQL command:

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')

I believe MongoDB also has support for this via similar commands.

Or should I open a new issue for this?

[WeihanLi]

Hello :)

Would you also consider adding support for modifying JSON in the DB without first retrieving the full JSON?

For example, T-SQL supports "in situ" modification of JSON via the JSON_MODIFY() T-SQL command:

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'

PRINT @info

-- Update name  

SET @info=JSON_MODIFY(@info,'$.name','Mike')

I believe MongoDB also has support for this via similar commands.

Or should I open a new issue for this?

wanna this feature also, seemed a new issue is better for tracking

[flopgiggle]

We use json columns in a large number of medical scenarios. Now we migrate to sql server and find that ef does not support json access. We hope to implement it sooner.

[ronnyek]

IMO this would be absolutely huge. I've been spoiled with npgsql support. The more standard the JSON column stuff can be, the better.

[neozhu]

hi, everyone, why don't I see builder.Entity(entity => { entity.Property(x => x.Name) .HasJsonValue() this option in my project

[knuxbbs]

hi, everyone, why don't I see builder.Entity(entity => { entity.Property(x => x.Name) .HasJsonValue() this option in my project

Because this was not implemented in EF Core.

[neozhu]

@knuxbbs will be implemented it in ef core 6.0

[mguinness]

@neozhu See Plan for Entity Framework Core 6.0 for latest update.

Update: Looks like it was dropped from 6.0 according to Entity Framework Biweekly Status Updates (2021).

[mojtabakaviani]

Please implemented it in ef core 7.0

[atrauzzi]

Yeah, I'd really love to see this in 7.

I'd also like to see the ability to serialize types to and from JSON columns using some kind of embedded discriminator.

[Aloento]

Have you considered mapping to Dictionary<string, string>? Such as hstore within PostgreSQL.

[roji]

@Aloento how exactly EF will support JSON is still something we need to determine.

[roji]

See below for a bit of research on how JSON support looks like across databases today, plus stuff we could do for 7.0. This is supposed to provide some basis for further scoping/design decisions.

Provider documentation

• PostgreSQL types/general, functions/operators.
• SQLServer general, functions
• SQLite. Included by default (and with new functionality) in SQLite 3.38.0, probably to be released in 2022.
• MySQL type info, functions
• MariaDB type info, functions

General comments

• We should obviously allow mapping a composition of CLR POCO types to a JSON document (like we already do in Cosmos). But there's also the possibility to allow weakly-typed mapping, for cases where JSON document structure is different across different rows.
  • This could be done by mapping JsonDocument (see the Npgsql docs), at which point we could translate methods/properties on it.
  • There's also the option of mapping to Dictionary<string, object>.
  • Using C# dynamic could be interesting, but that's not supported in expression trees.
• Consider if we want to do CLR POCO mapping via the owned entity concept. This would likely be important for implementing partial updates.
• PostgreSQL and MySQL have special JSON types, with an optimized binary representation. SQL Server, SQLite and MariaDB just use their regular text type (but there are apparently investigations into the possibility of an optimized binary type as well).

Querying into JSON documents

PostgreSQL, MySQL and SQLite all support the -> and ->> operators, which allow extracting subtrees of a JSON document; -> extracts a JSON document (on which further JSON operations can be done), ->> extracts a non-JSON scalar value (e.g. string, int). They also support the json_extract function, which is similar.

SQL Server has JSON_VALUE and JSON_QUERY, which are similar (comparison).

In some cases these accept a jsonpath expression, in others a set of simple keys (as text) to navigate.

jsonpath

jsonpath is a pretty standard expression syntax for querying a JSON document, e.g.:

$.store.book[0].title

jsonpath is supported to varying extents in all the databases; in some databases it's the standard (and only way) to drill into a document.

jsonpath allows full querying as well, at least on some databases:

$.track.segments[*].HR ? (@ > 130)

(retrieve heart rate values higher than 130)

In theory we could translate LINQ operators over JSON POCOs to the above jsonpath, but support across databases (and value) still need to be researched.

Note that System.Text.Json doesn't currently support jsonpath, but is planned to in 7.0 (issue). When using weakly-typed mapping, we could translate jsonpath APIs on JsonDocument/JsonElement to the appropriate database JSON query.

Partial updates

With partial updates, changing a value deep inside the JSON can be done efficiently by sending the appropriate update operation to the database, rather than replacing the entire column value. All databases support this functionality.

• MySQL: JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE (same in MariaDB).
• PostgreSQL: jsonb_set, jsonb_set_lax, jsonb_insert, for delete there's the #- operator.
• SQL Server: JSON_MODIFY
• SQLite: json_set, json_insert, json_replace, json_remove.

Notes

• We should probably concentrate on support via System.Text.Json, and leave out Newtonsoft.Json (though we may be able to make something pluggable).
• Returning relational results as JSON (e.g. SQL Server FOR JSON): probably not in scope.
• In the other direction, databases usually have functionality for converting JSON into a relational result set (e.g. SQL Server's OPENJSON). Probably not in scope as well.
• Databases have various mechanisms for creating indexes into JSON document, but these are unlikely to be cross-database. We can research further, but it may be sufficient to have users deal with this manually at first.

/cc @maumar

[atrauzzi]

Does the POCO mapping you mention also include using discriminators?

Something that would be very useful is if a JSON mapped column is dynamic or has a known type - whether it's an interface, a class or perhaps in the future a union type - would be to allow LINQ to generate provider specific JSON filtering.

db.MyEntities.Where((myEntity) => myEntity.JsonMappedField.Name == "Alex");

EF has everything it needs to notice that the field being filtered is mapped as a JSON column and translate that to the appropriate where syntax in the query it generates. :confetti_ball:

Could probably even mix it in with some of C#s pattern matching like is for things like the @> operator in postgres?

A way to use all the JSON operators in postgres from LINQ would be - :pinched_fingers: :kissing_closed_eyes: so good!

[GeroL]

I would like to see support for mapping JsonNode. I find it easier to work with than JsonDocument.

[marchy]

How is this feature tracking for the EF7 release? Any expectation on when we can start getting our hands on it?

[aaronhudon]

How is this feature tracking for the EF7 release? Any expectation on when we can start getting our hands on it?

7 or 8 more years. Just be patient.

[marchy]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long!

Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

[aaronhudon]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long!

Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

[marchy]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long! Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

Well... we DID have a migration to Postgres scheduled for this year so we can take advantage of the Postgres provider's implementation of this, but now with the announcement of its standardization we figured we'll wait for it instead.

Of course, that is, unless it doesn't drop this year.

Which brings me back to my question....

[mguinness]

Of course, that is, unless it doesn't drop this year.

I doubt you will get any type of commitment at this stage. Keep an eye on Plan for Entity Framework Core 7.0 and 7.0.0 milestone and hope it stays in - https://github.com/dotnet/efcore/issues/13947 was recently punted.

[stevendarby]

How is this feature tracking for the EF7 release?

Any expectation on when we can start getting our hands on it?

There's a draft PR with initial work https://github.com/dotnet/efcore/pull/28171

[mguinness]

That's promising, but a draft PR https://github.com/dotnet/efcore/pull/24909 with initial work for value conversions was subsquently punted. Hopefully JSON columns makes it into preview 7 or 8, otherwise it's cutting it close for the November release.

[aaronhudon]

Hahah. Well it's listed as the top/first feature int the EF 7 roadmap (link link)... so I certainly hope not that long! Feels like a good time to check-in to see if it's still tracking for this year's release or not – which is the material question at hand.

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

Well... we DID have a migration to Postgres scheduled for this year so we can take advantage of the Postgres provider's implementation of this, but now with the announcement of its standardization we figured we'll wait for it instead.

Of course, that is, unless it doesn't drop this year.

Which brings me back to my question....

Switch to Postgres (if you can) and don’t look back.

[MeepTech]

It’s embarrassing how long they’ve taken. It’s been years now. Those who needed have already implemented their own workarounds by using function references.

Could you possibly explain how or know any links about this? I've been looking myself.