Using Marten with PostgreSQL as Document DB

USE JSONB DATA TYPE EFFECTIVELY IN YOUR OWN PROJECTS!

This article aims to provide a very basic explanation of PostgreSQL-Marten combination and JSONB data type. Hopefully, after reading this, you’ll have some understanding of what the power behind these terms is and how to use JSONB data type effectively in your own projects.

1. WHAT IS MARTEN?

Marten is a C# library that uses Postgres as a NoSQL data store.

Now you wonder – why would I transform a rock-solid RDBMS into NoSQL data store, when I have pure NoSQL DBs at my disposal, like MongoDB or DynamoDB?

The answer is simple: you’ll get the synergy of two worlds, RDBMS and NoSQL. You can store normalized data as in regular relational DB, while persisting documents in JSONB format as in NoSQL DB.

2. WHAT IS JSONB?

JSONB stands for JSON Binary and represents an improved version of JSON data type. It’s a decomposed binary format to store JSON. A few benefits in comparison to its older brother, raw JSON, are:

• much faster to process,
• has a support for indexing.

There are some drawbacks too:

• slower insert time due to conversion,
• more disk space usage.

As a rule of thumb, you should be using JSONB instead of JSON for document storage.

3. HOW TO USE JSONB DATA?

Now, to the meat of this document – JSONB manipulation and querying!

First, let’s create a new table, called “person”:

create table person (

id serial not null primary key,

info jsonb not null

);

The table is very simple, consisting of only 2 columns – “id” and “info”. Notice that we used JSONB as type for the “info” column.

Let’s add a person into our table:

insert into person values

(1, ‘{“name”: “Mark”, “address”: {“city”: “Belgrade”, “street”: “Main Street 38”}}’);

Still simple enough, adding an entry in respect of data types defined.

Now, consider the requirement to add another key/value pair called “country” to the existing “address”. We need to use “jsonb_set” function, specific to JSONB data type:

update person set 

info = jsonb_set(

info,

‘{address}’,

(info->’address’) || ‘{“country”:”Serbia”}’

 )

where id = 1;

Let’s stop here for a bit. There are a few new things to consider when using “jsonb_set” function:

• it accepts at least 3 arguments – target, path to change and new value,
• 1st argument is the actual JSONB column we wish to change,
• 2nd argument is a path to the field within JSONB we wish to change,
• 3rd argument is a new value we wish to set,
• “||” is an operator used to concatenate existing data with new data; it will either update or insert the key to the existing document.

So, what about querying? First, let’s add two more entries to our table:

insert into person values

(2, ‘{“name”: “Mary”, “address”: {“city”: “Novi Sad”, “street”: “Main Ave 5”, “country”: “Serbia”}}’);

insert into person values

(3, ‘{“name”: “Jessica”, “address”: {“city”: “Stockholm”, “street”: “Central Street 57”, “country”: “Sweden”}}’);

Now, consider the request to get name and city of every person living in Serbia. We need to make use of “->” and “->>” JSONB operators:

select info->’name’ as name, info->’address’->’city’ as city

from person

where info->’address’->>’country’ = ‘Serbia’

The operator “->” returns a JSONB object and is used to navigate through the document, while the operator “->>” returns text and is used to compare the value from the key/value pair with the string constant supplied.

The above query will return Mark from Belgrade and Mary from Novi Sad.

These are the basics! Hopefully, you’ll enjoy using JSONB as document data type as much as I do. If you need any help from our team, you know where to find us. Happy coding!

For more useful updates make sure to follow our Facebook, Instagramand LinkedIN profiles!

Written by: Marko Đidara