support for JSON operator ?
Posted: Wed 10 Dec 2014 07:05
Hello, I would like to use Postgres 9.4 JSON operator ?
e.g. I would like to call the following SQL query using DataContext.ExecuteQuery<Book>:
The call causes the exception:
I am able to call another statement without any error:
Is there some problem with question mark operator?
Thank you for help.
DotConnect generated code (I use string Data instead of json type, which is not available in LinqConnect):
Database:
e.g. I would like to call the following SQL query using DataContext.ExecuteQuery<Book>:
Code: Select all
SELECT * FROM "Books" WHERE "Data"::jsonb ? 'publisher'Code: Select all
Devart.Data.PostgreSql.PgSqlException : Parameter '$1' is missing
Code: Select all
SELECT * FROM "Books" WHERE "Data"->'author'->>'first_name' = 'Charles'Thank you for help.
DotConnect generated code (I use string Data instead of json type, which is not available in LinqConnect):
Code: Select all
class Book
{
[Column(Name = @"""Id""", Storage = "_Id", CanBeNull = false, DbType = "serial NOT NULL", IsDbGenerated = true, IsPrimaryKey = true)]
public int Id {...}
[Column(Name = @"""Data""", Storage = "_Data", CanBeNull = false, DbType = "varchar NOT NULL", UpdateCheck = UpdateCheck.Never)]
public string Data{...}
}
Code: Select all
CREATE TABLE "Books" ( "Id" integer primary key, "Data" json );
INSERT INTO "Books" VALUES (1,
'{ "name": "Book the First", "author": { "first_name": "Bob", "last_name": "White" } }');
INSERT INTO "Books" VALUES (2,
'{ "name": "Book the Second", "author": { "first_name": "Charles", "last_name": "Xavier" } }');
INSERT INTO "Books" VALUES (3,
'{ "name": "Book the Third", "author": { "first_name": "Jim", "last_name": "Brown" } }');
INSERT INTO "Books" VALUES (5,
'{ "name": "Book the Fifth", "author": { "first_name": "Joe", "last_name": "Viviani" }, "publisher": "GRADA" }');