json vs jsonb postgresql

Question

json vs jsonb postgresql

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

What is the difference between json and jsonb ?

The @EmilioPlatzer user gave me an answer to another question where he uses these 2, but I do not see the difference.

In fact, the user comments on the following:

json vs jsonb

For the purposes of this problem it does not matter, you can use array or object in both variants. I recommend jsonb, but that's another topic.

Here is a fiddler where I use json and here a fiddler where I use jsonb

Personally I do not see any difference, nor in sql response time.

sql postgresql

asked by Pablo Contreras 30.04.2017 в 18:13

source

2 answers

2

Summary

The difference is internal. What is stored and when is the conversion made?

json : store the text, the conversion is done in each SELECT , advantages: the insert is faster, text is stored what is specified (and there is not a single way to write a JSON object ).
jsonb : store the object, the conversion is done in each INSERT , advantages: the selects are faster, what is stored is standardized

You should prefer jsonb in all cases, unless the INSERT is your critical operation.

answered by 30.04.2017 в 18:24

Problem removing elements from an array How to add parameters to a button from 'Java Code' in Android studio

score 1 · Accepted Answer

jsonb stores data in a binary format, also allows indexes. While the format json saves the text, including the spaces. Due to the conversion the format jsonb can be a bit slow when saving it , but thanks to its index it is much faster in searches.

The Postgres documentation itself indicates that unless there is a specific reason for use the other type, you should prefer jsonb .