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
487
votes
9 answers

Explanation of JSONB introduced by PostgreSQL

PostgreSQL just introduced JSONB in version 9.4, and it's already trending on hacker news. How is it different from Hstore and JSON previously present in PostgreSQL? What are its advantages and limitations and when should someone consider using it?
Peeyush
  • 6,144
  • 5
  • 23
  • 37
238
votes
11 answers

How to perform update operations on columns of type JSONB in Postgres 9.4

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions:…
jvous
  • 2,383
  • 2
  • 12
  • 4
213
votes
2 answers

Query for array elements inside JSON type

I'm trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], …
pacothelovetaco
  • 2,361
  • 3
  • 16
  • 12
108
votes
4 answers

Postgresql json like query

I have the following table called module_data. Currently it has three rows of entries: id data 0ab5203b-9157-4934-8aba-1512afb0abd0 {"title":"Board of Supervisors…
adviner
  • 3,295
  • 10
  • 35
  • 64
108
votes
1 answer

Index for finding an element in a JSON array

I have a table that looks like this: CREATE TABLE tracks (id SERIAL, artists JSON); INSERT INTO tracks (id, artists) VALUES (1, '[{"name": "blink-182"}]'); INSERT INTO tracks (id, artists) VALUES (2, '[{"name": "The Dirty Heads"}, {"name":…
JeffS
  • 2,647
  • 2
  • 19
  • 24
92
votes
9 answers

How to query a json column for empty objects?

Looking to find all rows where a certain json column contains an empty object, {}. This is possible with JSON arrays, or if I am looking for a specific key in the object. But I just want to know if the object is empty. Can't seem to find an operator…
sbeam
  • 4,622
  • 6
  • 33
  • 43
90
votes
1 answer

Calculate JSONB Array Length Using PostgreSQL 9.4

I'm running the latest version of PostgreSQL 9.4.5-1.pgdg14.04+1, and am attempting to calculate the length of a JSONB array using the JSON_ARRAY_LENGTH function as described in the PostgreSQL 9.4 Documentation Here is the exact query I'm attempting…
Joshua Burns
  • 8,268
  • 4
  • 48
  • 61
81
votes
4 answers

Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+

For versions less than 9.5 see this question I have created a table in PostgreSQL using this: CREATE TEMP TABLE jsontesting AS SELECT id, jsondata::jsonb FROM ( VALUES (1, '["abra","value","mango", "apple", "sample"]'), (2,…
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
72
votes
2 answers

Difference between JSON and JSONB in Postgres

What's difference between JSON and JSONB data type in PosgresSQL? When should be used specific one? What's benefits or disadvantages with respect to other?
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
71
votes
2 answers

postgres jsonb_set multiple keys update

I have DB table with jsonb column. number | data 1 | {"name": "firstName", "city": "toronto", "province": "ON"} I need a way to update data column. So my output should look like: {"name": "firstName", "city": "ottawa", "province": "ON",…
stack_d_code
  • 1,196
  • 2
  • 12
  • 19
65
votes
1 answer

How do I search for a specific string in a JSON Postgres data type column?

I have a column named params in a table named reports which contains JSON. I need to find which rows contain the text 'authVar' anywhere in the JSON array. I don't know the path or level in which the text could appear. I want to just search through…
Joshua Dance
  • 8,847
  • 4
  • 67
  • 72
65
votes
7 answers

How to convert PostgreSQL 9.4's jsonb type to float

I'm trying the following query: SELECT (json_data->'position'->'lat') + 1.0 AS lat FROM updates LIMIT 5; (The +1.0 is just there to force conversion to float. My actual queries are far more complex, this query is just a test case for the…
fadedbee
  • 42,671
  • 44
  • 178
  • 308
64
votes
3 answers

PostgreSQL rename attribute in jsonb field

In postgresql 9.5, is there a way to rename an attribute in a jsonb field? For example: { "nme" : "test" } should be renamed to { "name" : "test"}
T. Kong
  • 643
  • 1
  • 5
  • 5
61
votes
2 answers

How to get size of PostgreSQL jsonb field?

I have a table with jsonb field in table. CREATE TABLE data.items ( id serial NOT NULL, datab jsonb ) How to get size of this field in a query like this: select id, size(datab) from data.items
mystdeim
  • 4,802
  • 11
  • 49
  • 77
59
votes
2 answers

postgresql migrating JSON to JSONB

In postgresql 9.4 the new JSONB was incorporated. On a live DB in postgresql 9.3 I have a JSON column. I want to migrate it to JSONB. Assuming I migrated the DB first to 9.4 (using pg_upgrade). What do I do next?
Boaz
  • 4,864
  • 12
  • 50
  • 90
1
2 3
…
99 100