JSON support in DotConnect

JSON support in DotConnect

Postby msimko » Fri 22 Aug 2014 11:44

Hello, could you please provide a simple tutorial for querying data with JSON objects? I was not able to find any information about how to do it using DotConnect for Postgres.
I woule like to use LinqConnect model.

I have a simple table with two columns:
Code: Select all
CREATE TABLE books ( id integer primary key, data json );

containing some data
Code: Select all
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" } }');

How is it possible to perform the following queries using linqconnect data context?
Code: Select all
SELECT id, data->'name' as book, data->'publisher' as publisher FROM books;


Thank you.
msimko
 
Posts: 9
Joined: Thu 06 Feb 2014 09:40

Re: JSON support in DotConnect

Postby MariiaI » Tue 26 Aug 2014 12:38

The possible ways are:
1) use the code like this:
Code: Select all
var results = (from c in ctx.Books
                               select new
                               {
                                   id = c.Id,
                                   book = c.Data
                               }).ToList();       
           
            foreach (var result in results)
            {
                int indexName = result.book.IndexOf("name");
                int indexAutor = result.book.IndexOf("author");
                Console.WriteLine("id: " + result.id + "; name: " + result.book.Substring(indexName, indexAutor - indexName) + "; author: " + result.book.Substring(indexAutor) + "\n");
            }


2) or use the plain SQL via PgSqlCommand.

If you have any further questions, feel free to contact us.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: JSON support in DotConnect

Postby msimko » Tue 26 Aug 2014 13:29

Thank you for reply. I know about these two options.
First one is not usable when filtering (Postgres allows fast filtering on JSON data).
I thought there was direct JSON support. Feature list for dotConnect 7.3 (06-March-14) says JSON data type is supported in LinqConnect.

I would like to do something like

Code: Select all
ctx.Books.Select(b => new Tuple<string, string>(b.data["name"], b.data["publisher"]))

which will be transcoded to
Code: Select all
select id, data->'name', data->'publisher' from books


or something with comparisons, e.g.

Code: Select all
ctx.Books.Where(b => b.data["name"].Contains("DotConnect")).Select(b => b.data["name"])

transcoded to sql code that use PostgreSql JSON capabilities (not iterating whole dataset and parsing and comparing whole b.data as a string). PostgreSql allows indexing on JSON data, I would like to use such features.

Is the plain SQL the only way when using LINQ to SQL?
msimko
 
Posts: 9
Joined: Thu 06 Feb 2014 09:40

Re: JSON support in DotConnect

Postby MariiaI » Thu 28 Aug 2014 12:11

PostgreSql allows indexing on JSON data, I would like to use such features.

Unfortunately, there is no way to directly use this functionality when working with LinqConnect, except plain SQL and filtering data after receiving it.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL