Devart Blog

JSON Support in Entity Framework v4 – v6 in dotConnect for Oracle, MySQL, and PostgreSQL

Posted by on December 8th, 2016

Recent versions of MySQL and PostgreSQL databases provide JSON functionality support. They introduced special data types, storing JSON data – json data type in MySQL (since MySQL 5.7.8), and json and jsonb data types in PostgreSQL (json introduced in PostgreSQL 9.2, and jsonb introduced in PostgreSQL 9.4).

In Oracle, some JSON-related functionality was added in version 12c. Oracle Database currently does not offer a specific data type, and you should store JSON in usual CLOB and NCLOB columns.

Among other improvements, the new versions of dotConnect for Oracle, dotConnect for MySQL, and dotConnect for PostgreSQL offer support of JSON-related functionality of these databases in Entity Framework. In Entity Framework v4, v5, and v6, you can call database-specific JSON-related functions and operators in LINQ to Entities queries.

Let’s see how to do it on a simple examples:

Model

Here is the code of the model, which maps an entity to a simple table with a JSON column. The column is mapped to a property of the System.String type.

public class MyContext: DbContext {
 
  public DbSet JsonTables { get; set; }
 
}
 
public class JsonTable {
 
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Id { get; set; }
 
  // Mapping for Oracle
  [Column(TypeName = "nclob")]
  // Mapping for MySQL 5.7.8 and higher
  [Column(TypeName = "json")]
  // Mapping for PostgreSQL 9.4 and higher
  [Column(TypeName = "jsonb")]
  // Mapping for PostgreSQL 9.2 and higher
  [Column(TypeName = "json")]
  public string JObject { get; set; }
 
  public string Text { get; set; }
 
}

Database Table Creation

To create the corresponding table in the database, use the following code:

var ctx = new MyContext();
ctx.Database.Create();

This code will create the following table

Oracle

CREATE TABLE "JsonTable" ( 
  "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
  "JObject" NCLOB NULL,
  "Text" NCLOB NULL,
  CONSTRAINT "PK_JsonTable" PRIMARY KEY ("Id")
)

MySQL

CREATE TABLE JsonTable ( 
  Id int AUTO_INCREMENT UNIQUE NOT NULL,
  JObject json NULL,
  `Text` longtext NULL,
  CONSTRAINT PK_JsonTable PRIMARY KEY (Id)
)

PostgreSQL

CREATE TABLE "JsonTable" ( 
  "Id" serial NOT NULL,
  "JObject" jsonb NULL,
  "Text" text NULL,
  CONSTRAINT "PK_JsonTable" PRIMARY KEY ("Id")
)

LINQ to Entities Queries

Oracle

dotConnect for Oracle allows using Oracle JSON functions in LINQ to Entities queries via the OracleJsonFunctions class. The OracleJsonFunctions class contains the following methods:

OracleJsonFunctions methods Corresponding Oracle Functions and Operators Description
IsJson IS JSON Checks whether some data is well-formed JSON data.
Exists JSON_EXISTS Checks whether a particular value exists within some JSON data.
TextContains JSON_TEXTCONTAINS Checks whether a specified string exists in JSON property values.
Value JSON_VALUE Select a scalar value from JSON data as an SQL value.

 

For example, the following query retrieves rows where the specified JSON value (named “a”) exists in a stored JSON object and selects this value.

var query = context.JsonTables
  .Where(t => OracleJsonFunctions.Exists(t.JObject, "$.a"))
  .Select(t => new
  {
    Id = t.Id,
    Json = t.JObject,
    Value = OracleJsonFunctions.Value(t.JObject, "$.a")
  });

The following SQL query will be generated for this LINQ to Entities query:

SELECT 
"Extent1"."Id",
"Extent1"."JObject",
JSON_VALUE("Extent1"."JObject", '$.a') AS C1
FROM "JsonTable" "Extent1"
WHERE JSON_EXISTS("Extent1"."JObject", '$.a')

MySQL

dotConnect for MySQL allows using MySQL JSON functions in LINQ to Entities queries via the MySqlJsonFunctions class. The MySqlJsonFunctions class contains the following methods:

MySqlJsonFunctions methods Corresponding MySQL Functions and Operators Description
Array JSON_ARRAY Returns a JSON array, composed of arguments.
Length JSON_LENGTH Returns the number of elements in the outermost JSON array or the number of members in the outermost JSON objects or 1 for a scalar.
Depth JSON_DEPTH Returns the maximum depth of a JSON document. Returns NULL if the argument is NULL.
Type JSON_TYPE Returns the type of the outermost JSON value as a string.
Extract JSON_EXTRACT(): equivalent to the -> operator Returns data from a JSON document by the specified path.
ExtractUnquotedText JSON_UNQUOTE(JSON_EXTRACT()): equivalent to the ->> operator Returns unquoted data from a JSON document by the specified path.
Quote JSON_QUOTE Quotes the specified string with double quotetion marks and escapes interior quote and other characters in the string in order to make the specified string a valid JSON string literal.
Unquote JSON_UNQUOTE Return an unquoted value of the JSON string literal.
Valid JSON_VALID Returns 1 if a valid JSON document is specified, or 0 if an invalid JSON document is specified. Returns NULL if the argument is NULL.

 

For example, the following query demonstrates using various MySQL specific JSON-related functions and operators.

string jsonValue = "{f0:0,f1:12}";
 
var query = context.JsonTables
  .Where(t => MySqlJsonFunctions.ExtractUnquotedText(t.JObject, "$.a") == "foo")
  .Select(t => new
  {
    Id = t.Id,
    Json = t.JObject,
    Depth = MySqlJsonFunctions.Depth(t.JObject),
    Length = MySqlJsonFunctions.Length(t.JObject),
    Field = MySqlJsonFunctions.Extract(t.JObject, "$.a"),
    UnquotedField = MySqlJsonFunctions.ExtractUnquotedText(t.JObject, "$.a"),
    ObjectType = MySqlJsonFunctions.Type(t.JObject),
    FieldType = MySqlJsonFunctions.Type(MySqlJsonFunctions.Extract(t.JObject, "$.a")),
    Quote = MySqlJsonFunctions.Quote(jsonValue),
    Valid = MySqlJsonFunctions.Valid(jsonValue),
    ValidQuoted = MySqlJsonFunctions.Valid(MySqlJsonFunctions.Quote(jsonValue))
  });

The following SQL query will be generated for this LINQ to Entities query:

SELECT 
Extent1.Id,
Extent1.JObject,
json_depth(Extent1.JObject) AS C1,
json_length(Extent1.JObject) AS C2,
json_extract(Extent1.JObject, '$.a') AS C3,
json_unquote(json_extract(Extent1.JObject, '$.a')) AS C4,
json_type(Extent1.JObject) AS C5,
json_type(json_extract(Extent1.JObject, '$.a')) AS C6,
json_quote(:p__linq__0) AS C7,
json_valid(:p__linq__1) AS C8,
json_valid(json_quote(:p__linq__2)) AS C9
FROM JsonTables AS Extent1
WHERE (json_unquote(json_extract(Extent1.JObject, '$.a'))) = 'foo'

PostgreSQL

dotConnect for PostgreSQL allows using PostgreSQL JSON functionality of json and jsonb data types in LINQ to Entities queries via the PgSqlJsonFunctions and PgSqlJsonbFunctions classes respectively.

Please note that the PgSqlJsonFunctions class is supported for columns of json types on PostgreSQL 9.2 and higher, and PgSqlJsonbFunctions class is supported for columns of jsonb type on PostgreSQL 9.4 and higher.

The PgSqlJsonFunctions class has the following methods:

PgSqlJsonFunctions methods Corresponding PostgreSQL Functions and Operators Description
ToJson TO_JSON Returns the value as json. It converts arrays and composite objects to JSON arrays and objects. Otherwise, if there is a cast from the type to json, this cast function will be used to perform the conversion; otherwise, returns a valid scalar json value. For any scalar type other than a number, a Boolean, or a null value, the text representation is used.
JsonObject JSON_OBJECT Creates a JSON object from a text array. Array must either have one dimension and even number of elements, in this case these elements are taken as key/value pairs, or two dimensions, with each inner array having two elements. In the latter case inner arrays are taken as key/value pairs.
BuildArray JSON_BUILD_ARRAY Builds a JSON array of the arguments.
ArrayLength JSON_ARRAY_LENGTH Returns the number of elements in the outermost JSON array.
TypeOf JSON_TYPEOF Returns the type of the outermost JSON value as a string. Possible return values are: object, array, string, number, boolean, and null.
ExtractPath JSON_EXTRACT_PATH: equivalent to the #> operator Returns a JSON object at the specified path.
ExtractPathText JSON_EXTRACT_PATH_TEXT: equivalent to the #>> operator Returns a JSON object at the specified path as text.
GetObjectField equivalent to the -> operator Returns an onject field value by its key.
GetObjectFieldAsText equivalent to the ->> operator Returns an onject field value as text by its key.
GetArrayElement equivalent to the -> operator Returns an array element by a zero-based index.
GetArrayElementAsText equivalent to the ->> operator Returns an array element as text by a zero-based index.
StripNulls JSON_STRIP_NULLS Returns the specified JSON object with all fields that have null values omitted.

 

The PgSqlJsonbFunctions class has the following methods:

PgSqlJsonbFunctions methods Corresponding PostgreSQL Functions and Operators Description
ToJsonb TO_JSONB Returns the value as jsonb. It converts arrays and composite objects to JSON arrays and objects. Otherwise, if there is a cast from the type to jsonb, this cast function will be used to perform the conversion; otherwise, returns a valid scalar jsonb value. For any scalar type other than a number, a Boolean, or a null value, the text representation is used.
JsonbObject JSONB_OBJECT Creates a JSON object from a text array. Array must either have one dimension and even number of elements, in this case these elements are taken as key/value pairs, or two dimensions, with each inner array having two elements. In the latter case inner arrays are taken as key/value pairs.
BuildArray JSONB_BUILD_ARRAY Builds a JSON array of the arguments.
ArrayLength JSONB_ARRAY_LENGTH Returns the number of elements in the outermost JSON array.
TypeOf JSONB_TYPEOF Returns the type of the outermost JSON value as a string. Possible return values are: object, array, string, number, boolean, and null.
ExtractPath JSONB_EXTRACT_PATH: equivalent to the #> operator Returns a JSON object at the specified path.
ExtractPathText JSONB_EXTRACT_PATH_TEXT: equivalent to the #>> operator Returns a JSON object at the specified path as text.
GetObjectField equivalent to the -> operator Returns an onject field value by its key.
GetObjectFieldAsText equivalent to the ->> operator Returns an onject field value as text by its key.
GetArrayElement equivalent to the -> operator Returns an array element by a zero-based index.
GetArrayElementAsText equivalent to the ->> operator Returns an array element as text by a zero-based index.
StripNulls JSONB_STRIP_NULLS Returns the specified JSON object with all fields that have null values omitted.
Pretty JSONB_PRETTY Returns the specified JSON object as indented JSON text.

 

The following query demonstrates using various PostgreSQL specific JSON-related functions and operators.

var query = context.JsonTables
  .Where(t => PgSqlJsonbFunctions.GetObjectFieldAsText(t.JObject, "a") == "foo")
  .Select(t => new {
     Id = t.Id,
     Json = t.JObject,
     ObjectType = PgSqlJsonbFunctions.TypeOf(t.JObject),
     FieldType = PgSqlJsonbFunctions.TypeOf(PgSqlJsonbFunctions.ExtractPath(t.JObject, "a")),
     ExtractPath = PgSqlJsonbFunctions.ExtractPath(t.JObject, "a"),
     ExtractPathText = PgSqlJsonbFunctions.ExtractPathText(t.JObject, "a"),
     GetObjectField = PgSqlJsonbFunctions.GetObjectField(t.JObject, "a"),
     GetObjectFieldAsText = PgSqlJsonbFunctions.GetObjectFieldAsText(t.JObject, "a")
  });

The following SQL query will be generated for this LINQ to Entities query:

SELECT 
"Extent1"."Id",
"Extent1"."JObject",
jsonb_typeof("Extent1"."JObject") AS "C1",
jsonb_typeof(jsonb_extract_path("Extent1"."JObject", 'a')) AS "C2",
jsonb_extract_path("Extent1"."JObject", 'a') AS "C3",
jsonb_extract_path_text("Extent1"."JObject", 'a') AS "C4",
"Extent1"."JObject" -> 'a' AS "C5",
"Extent1"."JObject" ->> 'a' AS "C6"
FROM "JsonTables" AS "Extent1"
WHERE ("Extent1"."JObject" ->> 'a') = 'foo'
See also
Leave a Reply