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