Any high-level .NET clients for PostgreSQL's JSON type?

1.7k views Asked by At

We're evaluating PostgreSQL as a NoSQL doc-store using its json/jsonb datatype.

ElasticSearch has a very nice, high-level client library with NEST. In some ways similar to NHibernate, you can simply persist POCO classes, and queries can be done in a typeful way on properties of the class.

So far, the only client support I've found for Postgres around its json type is:

  • Use Npgsql or similar low-level client to get access to raw SQL, which can then use the special operators that Postgres supports for JSON querying.
  • Simply deserialize into a JSON object in my application and do any inner-object query work there. This is the method that would need to be used with NHibernate, for example, as far as I understand.

Are there any client libraries like NEST for Postgres's JSON datatype, that can use its special operators to allow queries and column/property selection to run in the database rather than doing them in my application?

1

There are 1 answers

0
Tim Cools On BEST ANSWER

Edit

I switched to Marten because it is more mature and has a bigger community support.

Original

I recently stumbled on elephanet. It is a RavenDb influenced .NET client for using PostgreSQL as a document database. Including support for json documents and jsonb indexing for fast retrieval.

DocumentStore store = new DocumentStore("Server=127.0.0.1;Port=5432;User Id=store_user;password=my super secret password;database=store;");

//create the object
var myAudi = new Car {
    Id = Guid.NewGuid(),
    Make = "Audi",
    Model = "A8",
    ImageUrl = "http://some_image_url",
    NumberPlate = "ABC029"
};

//save the object to the document store
using (var session = store.OpenSession())
{
    session.Store<Car>(myAudi);
    session.SaveChanges();
}