JSON support in DotConnect

[msimko]

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.

[MariiaI]

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.

[msimko]

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?

[MariiaI]

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.