support for JSON operator ?

support for JSON operator ?

Postby msimko » 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>:
Code: Select all
SELECT * FROM "Books" WHERE "Data"::jsonb ? 'publisher'

The call causes the exception:
Code: Select all
Devart.Data.PostgreSql.PgSqlException : Parameter '$1' is missing


I am able to call another statement without any error:
Code: Select all
SELECT * FROM "Books" WHERE "Data"->'author'->>'first_name' = 'Charles'


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):
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{...}
}


Database:
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" }');
msimko
 
Posts: 9
Joined: Thu 06 Feb 2014 09:40

Re: support for JSON operator ?

Postby msimko » Wed 10 Dec 2014 08:28

I have solved this particular problem by preparing stored procedure that executes the required statement and returns refcursor.

Still if there is possibility to do it more simple way, I appreciate the hint.
msimko
 
Posts: 9
Joined: Thu 06 Feb 2014 09:40

Re: support for JSON operator ?

Postby MariiaI » Wed 10 Dec 2014 11:40

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>:
SELECT * FROM "Books" WHERE "Data"::jsonb ? 'publisher'...

We plan to implement the JSONB support in dotConnect for PostgreSQL (LinqConnect/Entity Framework support). We will post here when it is implemented.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: support for JSON operator ?

Postby khorvat » Fri 27 Feb 2015 13:38

Hi,

did you implement support for JSONB in 7.3.3xx version ?

Thanks
khorvat
 
Posts: 6
Joined: Fri 13 Dec 2013 09:35

Re: support for JSON operator ?

Postby bobcardenas » Sun 01 Mar 2015 03:58

I am also interested to learn if JSONB support has been added. We would like to use this feature on our new web site.
bobcardenas
 
Posts: 2
Joined: Sun 01 Mar 2015 03:56

Re: support for JSON operator ?

Postby MariiaI » Mon 02 Mar 2015 12:18

We plan to add the JSONB support in one of the nearest builds of dotConnect for PostgreSQL. We will inform you when the corresponding build is available for download.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: support for JSON operator ?

Postby MariiaI » Fri 27 Mar 2015 07:11

The JSONB data type is supported (Entity Framework support).
New build of dotConnect for PostgreSQL 7.3.379 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=31496 .
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: support for JSON operator ?

Postby DarkCamper » Tue 07 Apr 2015 09:03

Hi, are you planning support for operator ? in the PgSqlCommand class in addition to the support in Entity Framework?

Not only it is used for json operations but also when using ltree searches and it would be really useful being able to execute simple queries without the requirement of using an ORM.
DarkCamper
 
Posts: 9
Joined: Wed 18 Mar 2015 17:31

Re: support for JSON operator ?

Postby Pinturiccio » Tue 07 Apr 2015 11:31

We will investigate the possibility to implement the ? operator for JSONB type and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: support for JSON operator ?

Postby Pinturiccio » Wed 08 Apr 2015 10:02

The reason of the issue is that dotConnect for PostgreSQL treats the '?' character in the command text as an unnamed parameter.

We have added the 'IgnoreUnnamedParameters' connection string parameter that disables unnamed parameters in queries, executed via the connection. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

If you set this parameter to true, the '?' characters won't be treated as unnamed parameters, and will be recognized as an operator for the JSONB type correctly.
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: support for JSON operator ?

Postby Pinturiccio » Fri 10 Apr 2015 09:04

New build of dotConnect for PostgreSQL 7.3.389 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=31567
Pinturiccio
Devart Team
 
Posts: 1982
Joined: Wed 02 Nov 2011 09:44

Re: support for JSON operator ?

Postby DarkCamper » Tue 14 Apr 2015 08:36

It works like a charm! Thank you!
DarkCamper
 
Posts: 9
Joined: Wed 18 Mar 2015 17:31

Re: support for JSON operator ?

Postby MariiaI » Thu 07 May 2015 11:56

The JSONB data type is supported (LinqConnect).
New build of dotConnect for PostgreSQL 7.3.407 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=31741.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL