Devart Forums

Posted: **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.

Posted: **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.

Posted: **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?

Posted: **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.

Devart Forums

JSON support in DotConnect

JSON support in DotConnect

Re: JSON support in DotConnect

Re: JSON support in DotConnect

Re: JSON support in DotConnect