JSON Support in Entity Framework

In This Topic

dotConnect for PostgreSQL provides support for JSON functionality, implemented in PostgreSQL 9.2 and later. It allows using PostgreSQL JSON functionality of json and jsonb data types in LINQ to Entities queries via the PgSqlJsonFunctions and PgSqlJsonbFunctions classes.

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.

Model

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

C#	Copy Code
public class MyContext: DbContext { public DbSet<JsonTable> JsonTables { get; set; } } public class JsonTable { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int Id { get; set; } // The JSON column. // Use this mapping for PostgreSQL 9.2 and 9.3 [Column(TypeName = "json")] // Use this mapping for PostgreSQL 9.4 and higher [Column(TypeName = "jsonb")] public string JObject { get; set; } public string Text { get; set; } }

Copy Code

public class MyContext: DbContext {
 
  public DbSet<JsonTable> JsonTables { get; set; }
 
}
 
public class JsonTable {
 
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Id { get; set; }
 
  // The JSON column. 
  // Use this mapping for PostgreSQL 9.2 and 9.3
  [Column(TypeName = "json")]
  // Use this mapping for PostgreSQL 9.4 and higher
  [Column(TypeName = "jsonb")]
  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:

C#	Copy Code
var ctx = new MyContext(); ctx.Database.Create();

This code will create the following table (for PostgreSQL 9.4 and higher):

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

JSON Features in LINQ to Entities

The PgSqlJsonFunctions class is intended for working with json PostgreSQL data type. It 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 is intended for working with jsonb PostgreSQL data type. It 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.

Example Query

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

C#	Copy Code
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") });

Copy Code

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

[email protected]