Stack Overflow
Stack Overflow

Questions tagged [jsonb]

Binary version of the json data type, used in Postgres 9.4+. The major practical difference is efficiency.

From the 9.4 manual *

There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

Because the json type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects. Also, if a JSON object within the value contains the same key more than once, all the key/value pairs are kept. (The processing functions consider the last value as the operative one.) By contrast, jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

(*) As 9.4 is currently in beta there is no "current" version of the manual for jsonb

2515 questions
53
votes
2 answers

Postgresql query array of objects in JSONB field

I have a table in a postgresql 9.4 database with a jsonb field called receivers. Some example rows: [{"id": "145119603", "name": "145119603", "type": 2}] [{"id": "1884595530", "name": "1884595530", "type": 1}] [{"id": "363058213", "name":…
user3761100
  • 553
  • 1
  • 4
  • 5
49
votes
1 answer

Operator does not exist: json = json

when I try to select some record from a table SELECT * FROM movie_test WHERE tags = ('["dramatic","women", "political"]'::json) The sql code cast a error LINE 1: SELECT * FROM movie_test WHERE tags = ('["dramatic","women",... …
Isaac
  • 689
  • 1
  • 6
  • 13
48
votes
1 answer

Postgresql query for objects in nested JSONB field

I am using PostgreSQL 9.6, and I have a table named "ItemDbModel" with two columns looks like: No integer, Content jsonb Say I put many records like: "No": 2, {"obj":"x","Item": {"Name": "BigDog", "Model": "NamedHusky", "Spec":"red dog"}} "No":…
ifdog
  • 541
  • 1
  • 4
  • 6
42
votes
2 answers

Upgrade PostgreSQL JSON column to JSONB?

After upgrading to PostgreSQL 9.4, how do I convert all my JSON columns into JSONB columns? I don't mind losing any duplicate keys and whitespace.
srlm
  • 3,186
  • 2
  • 27
  • 40
41
votes
1 answer

In postgresql, how can I return a boolean value instead of string on a jsonb key?

In the query below, $isComplete and $isValid are returned as a string. However, they are saved as boolean values. How can I get the boolean representation of these fields to be returned? query = "SELECT data #>> '{id}' AS id, …
dipole_moment
  • 5,266
  • 4
  • 39
  • 55
40
votes
4 answers

Extract key, value from json objects in Postgres

I have a Postgres table that has content similar to this: id | data 1 | {"a":"4", "b":"5"} 2 | {"a":"6", "b":"7"} 3 | {"a":"8", "b":"9"} The first column is an integer and the second is a json column. I want to be able to expand out the…
Tom G
  • 2,025
  • 3
  • 21
  • 32
38
votes
5 answers

how to store PostgreSQL jsonb using SpringBoot + JPA?

I'm working on a migration software that will consume unknown data from REST services. I already think about use MongoDB but I decide to not use it and use PostgreSQL. After read this I'm trying to implement it in my SpringBoot app using Spring JPA…
Magno C
  • 1,922
  • 4
  • 28
  • 53
37
votes
1 answer

Rails and jsonb type "jsonb" does not exist

psql --version psql (PostgreSQL) 9.4.1 rails -v Rails 4.2.0 I added a jsonb column through migration like that class AddPreferencesToUsers < ActiveRecord::Migration def change add_column :users, :preferences, :jsonb, null: false, default:…
medBouzid
  • 7,484
  • 10
  • 56
  • 86
35
votes
2 answers

In Django 1.9, what's the convention for using JSONField (native postgres jsonb)?

Django highly suggests not to use null=True for CharField and TextField string-based fields in order not to have two possible values for "no data" (assuming you're allowing empty strings with blank=True). This makes total sense to me and I do this…
Cloud Artisans
  • 4,016
  • 3
  • 30
  • 37
34
votes
3 answers

Is it possible to use Hibernate with PostgreSql's JSONB data type?

Hibernate 5 does not support the PostgreSQL jsonb data type by default. Is there any way to implement jsonb support for Hibernate + Spring JPA? If there is a way, what are the pros and cons of using jsonb with Hibernate?
Aventes
  • 569
  • 1
  • 8
  • 20
33
votes
4 answers

Postgres jsonb 'NOT contains' operator

I'm experimenting with postgres jsonb column types, and so far so good. One common query I'm using is like this: select count(*) from jsonbtest WHERE attributes @> '{"City":"Mesa"}'; How do I reverse that? Is there a different operator or is it…
user101289
  • 9,888
  • 15
  • 81
  • 148
31
votes
7 answers

PostgreSQL compare two jsonb objects

With PostgreSQL(v9.5), the JSONB formats give awesome opportunities. But now I'm stuck with what seems like a relatively simple operation; compare two jsonb objects; see what is different or missing in one document compared to the other. What I have…
Joost Döbken
  • 3,450
  • 2
  • 35
  • 79
30
votes
1 answer

ruby on rails jsonb column default value

I have a model ProjectKeyword where I use jsonb datatype in the column :segemnted_data class ProjectKeyword < ApplicationRecord belongs_to :project belongs_to :keyword has_many :project_keyword_dimensions has_many :dimensions, through:…
arthur-net
  • 1,138
  • 1
  • 13
  • 34
28
votes
2 answers

How to escape the ? (question mark) operator to query Postgresql JSONB type in Rails

I'm working with Rails 4.2 and Postgres 9.4 to try out the new JSONB data type. One of the JSONB columns in my database holds an array, and I want to be able to query for records where this array contains a certain value. I figured out how to do…
dfinn
  • 968
  • 1
  • 10
  • 16
27
votes
2 answers

Postgres GROUP BY on jsonb inner field

I am using Postgresql 9.4 and have a table test, with id::int and content::jsonb, as follows: id | content ----+----------------- 1 | {"a": {"b": 1}} 2 | {"a": {"b": 1}} 3 | {"a": {"b": 2}} 4 | {"a": {"c": 1}} How do I GROUP BY on an…
JGem
  • 377
  • 1
  • 3
  • 7
1
2
3
…
99 100