dotConnect for PostgreSQL Documentation
JSON Support in Entity Framework
support@devart.com

Glossary Item Box

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; }
 
}

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")
  });

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'