Differences between json and jsonb - JSON data type in PostgreSQL

-

You may have read differences between PostgreSQL datatypes JSON and JSONB. If not, let see the difference between both theoretically and practically.

Although, both json and jsonb are types of PostgreSQL JSON datatypes and accepts almost same format as input values i.e. json. But still there are some difference which one must know to efficiently use or can choose from both json and jsonb. 

The main different is how they actually store data; the json data type stores input value as it is but  jsonb stores value in a decomposed binary format which lead to difference in efficiency. 
Jsonb is slower as compare to json while saving the input values because of converting json values to binary format before saving.
Jsonb fast in processing the data because no re-parsing needed as compare to json datatype which needs processing function to re-parsing on every execution.

Few other differences between json and jsonb are:

  • Jsonb supports indexing which is advantage over json data type.
  • As json stores the value as it is, it keeps un-neccessary white space between tokens while jsonb convert json data to binary format it doesn't preserve the white-space.
  • Json keeps the order of keys in json object, Jsonb doesn't keeps order same as we pass the value.
  • As json store value as it is, so it keep duplicate keys too if input value have same key multiple times, But jsonb keeps last key-value pair in-case input have repeating key (because of conversion).

So from above, you got the idea which is better to use json or jsonb? I believe your answer will be jsonb. Of course! it has few more advantages over jsonb. 

How you decide practically, which one should use from json or jsonb?

From practical point of view, I have observed one should you JSON data type when:

  • You have do mostly read column as it is, no where clause e.g. saving data in for event logs
  • You have 1 dimensional associative Array or object and have to apply operations while reading column data

One should you JSONB data type when:

  • You have 2-dimensional associative array or object and have to apply operation while reading column data
  • You have array of associative arrays or objects and have to apply operation while reading column data


A man with small desires, likes things in simple conditions, loves god creation, feels pleasure to help someone needy.
Professional: Software Developer (PHP) (5+ yrs) with good hands in latest technologies and Frameworks like Laravel Cakephp 3, CodeIgniter, Wordpress, Core PHP, MySQL, PgSql, jQuery.

Comments

Post a Comment

Popular posts from this blog

Using Virtual Columns in Laravel - Accessors and Appends

-
What are virtual columns?  While developing any web application sometimes we need virtual columns - columns don't actually exist in our DB table, but we drive them from other columns. Why we need them? For example, generally, we use first_name and last_name  in the  users table. But sometimes we also need user's complete name i.e. full_name . There is no benefit of saving full_name in user table if we have already first_name and last_name in the table OR even if we have full_name  as a column in our table then there is no benefit of saving first_name and last_name in the table. So in a scenario like this, we prefer to use the virtual column. Using Virtual Columns in Laravel Considering the first case, in Laravel to get virtual column i.e.  full_name  from the users table will have Accessors. Accessors are methods defined automatically called by Eloquent when select is called on that particular column. Let take an example here: To get full_...
Read more

How to Show Cookie Policy Consent or GDPR Popup in Laravel using Cookie.

-
Cookie in Laravel You can use the cookie in Laravel using the Helper method ` cookie() ` or method available with Request/Response Object or `Cookie` facade. $request->cookie('name'); OR use Illuminate\Support\Facades\Cookie; Cookie::get('name'); To get more clarity on how to use the cookie in Laravel with an example to show cookie consent popup on your Laravel website and hide/remove it once you accept the policy. Cookie Policy Consent Popup in Laravel First, create and a Cookie Consent popup as notification to the footer section of your Laravel main blade template file.  <div class="cookies-block">         <h5>This website uses cookies</h5>         <p>We use cookies to improve your experience and deliver personalised content. By using this website, you agree to our <a href="javascript:void()">Cookie Policy</a>.</p>         ...
Read more

Postman Collection Run - How to Test File Uploading API on CircleCi or Jenkins

-
Image
From last couple of days,  I was doing R&D on how we can test file uploading API on CircleCi or Postman collection Run? Finally, I found a way to test the File uploading APIs using the Postman Desktop app  OR Postman CLI collection run. I have also tested this solution, even on CircleCi for running the automated testing postman APIs by Postman CLI collection run. Here is step by step process, how you can test the file uploading API on CircleCi or Postman collection Run? Local Desktop App settings: Open and go to Local Postman app settings -> General Set working directly location  ( default is postman installation directory but can also choose any other directory ) Place files (to use in file upload APIs during the collection run) in the working directory selected as in the above step and also in the root directory of your project. Now go to the API in which you need to upload a file, and select the file from the working directory selected in step 2. Save that ...
Read more