Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework for querying JSON strings in SQL Server

I'm looking for anyone who's done anything along the lines of querying JSON strings with the Entity Framework.

I should give a little background about what I'm trying to do here. The database I'm using is for a workflow engine that I'm working on. It handles all the workflow data, and also allows you to store some custom data as a JSON string. The workflow engine I'm using handles the serializing and de-serializing of the JSON strings on a per request basis, but in the event I would want to do a query and filter based on values in the JSON string, I would have to pull the entire table into memory and de-serialize all the entries and then filter. This is, for obvious reasons, not acceptable. The reason for doing this, is we want a single workflow database that can be used for all applications that use this workflow engine, and we are trying to avoid having to do cross database views to seperate application specific databases to get the custom data. Since in most cases, the custom request data that is being stored as JSON strings is relatively simple, and in most cases isn't needed when querying, which is by design. But in the event that we do need to do custom searches, we need a way to be able to parse these custom JSON objects. And I'm hoping this can be done dynamically with Entity so I don't have to write extra stored procs for querying specific types of objects. Ideally I would just have one library that uses entity to allow querying of any JSON data structure.

I started with a database function that I found that parses JSON and returns a flattened table that contains the values (parent object id, name, value, and type). Then imported that function into my entity model. Here's a link to where I got the code from. Pretty interesting article.

Consuming JSON Strings in SQL Server

Here's the basics of where I'm at.

using (var db = new WorkflowEntities()) {
    var objects = db.Requests.RequestData();
}

In the above code example, Request object is my base workflow Request object. RequestData() is an extension method on type

DbSet<Request>

and parseJSON is the name of my database function.

My plan is to write a series of extension methods that will filter the Queryables

IQueryable<parseJSON_result>

So for example, if I have an object that looks like this.

RequestDetail : {
    RequestNumber: '123',
    RequestType: 1,
    CustomerId: 1
}

I would be able to do something like

db.Request.RequestData().Where("RequestType", 1);

or something along those lines. The .Where method would Take RequestData(), which is an IQueryable that contains the parsed JSON, it would filter and return the new IQueryable result.

So my question really is, has anyone done anything like this? If so, what kind of approach have you taken? My original intent was to do something dictionary style, but it seemed too difficult. Any thoughts, ideas, suggestions, wisdom, would be much appreciated. I worked on this for awhile, and I feel like I really didn't get that far. Which is mostly just because I can't decide how I want the syntax to look, and I'm not sure if I should be doing more work database side.

This was my original idea for syntax, but I couldn't run the [] operator without hydrating the object.

db.Request.Where(req => req.RequestData()["RequestType"] == 1).Select(req => req.RequestData()["CustomerInfo"]);

I know this is a pretty long post, so if you've read this far, thanks for just taking the time to read the whole thing.

like image 580
Callan Avatar asked Jul 03 '13 04:07

Callan


People also ask

How do I query a JSON string in SQL?

To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

How can I get SQL data from JSON format?

Format query results as JSON, or export data from SQL Server as JSON, by adding the FOR JSON clause to a SELECT statement. Use the FOR JSON clause to simplify client applications by delegating the formatting of JSON output from the app to SQL Server.

Does SQL Server support JSON datatype?

You can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database.

Can you query a JSON?

You can query JSON data using a simple dot notation or, for more functionality, using SQL/JSON functions and conditions. You can create and query a data guide that summarizes the structure and type information of a set of JSON documents.


2 Answers

As of SQL Server 2016, FOR JSON and OPENJSON exist, equivalent to FOR XML and OPENXML. You can Index on expressions that reference JSON stored in NVARCHAR columns.

like image 84
Emyr Avatar answered Oct 21 '22 04:10

Emyr


This is a very late answer, but for anyone who is still searching...

As @Emyr says, SQL 2016 supports querying inside JSON columns using JSON_VALUE or OPENJSON statements.

Entity Framework still does not support this directly, but you can use the SqlQuery method to run a raw SQL command directly against the database which can query inside JSON columns and saves querying and deserializing every row in order to run a simple query.

like image 32
Nich Overend Avatar answered Oct 21 '22 05:10

Nich Overend