Explanation of JSONB introduced by PostgreSQL

by

First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be texts (however values can be sql NULLs too).

Both json & jsonb allows you to store a valid JSON value (defined in its spec).

F.ex. these are valid JSON representations: null, true, [1,false,"string",{"foo":"bar"}], {"foo":"bar","baz":[null]} – hstore is just a little subset compared to what JSON is capable (but if you only need this subset, it’s fine).

The only difference between json & jsonb is their storage:

  • json is stored in its plain text format, while
  • jsonb is stored in some binary representation

There are 3 major consequences of this:

  • jsonb usually takes more disk space to store than json (sometimes not)
  • jsonb takes more time to build from its input representation than json
  • json operations take significantly more time than jsonb (& parsing also needs to be done each time you do some operation at a json typed value)

When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them:

  1. If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use json.
  2. If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use jsonb.

More Related Contents:

  1. How do I modify fields inside the new PostgreSQL JSON datatype?
  2. Flatten aggregated key/value pairs from a JSONB field?
  3. How to query a json column for empty objects?
  4. Merging JSONB values in PostgreSQL?
  5. In postgresql, how can I return a boolean value instead of string on a jsonb key?
  6. Index for finding an element in a JSON array
  7. Query for array elements inside JSON type
  8. Is there a query language for JSON?
  9. Postgres: How to convert a json string to text?
  10. PostgreSQL return result set as JSON array?
  11. Using jsonb_set() for updating specific jsonb array value
  12. How to create index on JSON field in Postgres?
  13. How to convert postgres json to integer
  14. Postgres JSON data type Rails query
  15. Check if a Postgres JSON array contains a string
  16. Collect Recursive JSON Keys In Postgres
  17. Size limit of JSON data type in PostgreSQL
  18. Operator does not exist: json = json
  19. What are the differences between JSON and JSONP?
  20. How to parse JSON in Scala using standard Scala classes?
  21. Swift Codable with dynamic keys
  22. How do I customize default error message from spring @Valid validation?
  23. Prettify json in powershell 3
  24. Modify default JSON error response from Spring Boot Rest Controller
  25. Spring @ResponseBody Jackson JsonSerializer with JodaTime
  26. How to escape question mark (?) character with Spring JpaRepository
  27. How to open Visual Studio Code’s ‘settings.json’ file?
  28. unmarshalling arbitrary data
  29. REST API Best practices: args in query string vs in request body
  30. How to POST a JSON object to a JAX-RS service

Leave a Comment