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
27
votes
3 answers

Store accessor for nested JSON

I have the model "Organization" that stores all information related to an organization. There is a field of type JSONB named "integrations" that stores information pertaining to all external service integrations that an organization has. How do I…
Michael Victor
  • 861
  • 2
  • 18
  • 42
26
votes
2 answers

insert jsonb data in postgresql, option array or objects, valid way

I have this update, i've read postgresql documentation, but nothing clear about how to insert data, some tutorials options: 1.with '{}' 2.with {} 3.with '[]' <-- array of objects and most dont' use '::jsonb' like is indicated…
DDave
  • 1,400
  • 3
  • 16
  • 33
26
votes
2 answers

What's the proper index for querying structures in arrays in Postgres jsonb?

I'm experimenting with keeping values like the following in a Postgres jsonb field in Postgres 9.4: [{"event_slug":"test_1","start_time":"2014-10-08","end_time":"2014-10-12"}, …
Tony
  • 18,776
  • 31
  • 129
  • 193
24
votes
2 answers

Does JSONB make PostgreSQL arrays useless?

Suppose that you want to store "tags" on your object (say, a post). With release 9.4 you have 3 main choices: tags as text[] tags as jsonb tags as text (and you store a JSON string as text) In many cases, 3rd would be out of question since…
comte
  • 3,092
  • 5
  • 25
  • 41
24
votes
2 answers

PostgreSQL - jsonb_each

I have just started to play around with jsonb on postgres and finding examples hard to find online as it is a relatively new concept.I am trying to use jsonb_each_text to printout a table of keys and values but get a csv's in a single column. I…
Alan Mulligan
  • 1,107
  • 2
  • 16
  • 35
23
votes
1 answer

Using jsonb_set() for updating specific jsonb array value

Currently I am working with PostgreSQL 9.5 and try to update a value inside an array of a jsonb field. But I am unable to get the index of the selected value My table just looks like this: CREATE TABLE samples ( id serial, sample jsonb …
Daniel Seichter
  • 809
  • 1
  • 8
  • 14
23
votes
1 answer

When should HStoreField be used instead of JSONField?

Django 1.8 provides HStoreField and Django 1.9 will provide JSONField (which uses jsonb) for PostgreSQL. My understanding is that hstore is faster than json, but does not allow nesting and only allows strings. When should one be used over the other?…
mcastle
  • 2,882
  • 3
  • 25
  • 43
22
votes
1 answer

Detect if a jsonb attribute is array or object

Many jsonb/json functions expect all values of the column either to be of type json array ( like jsonb_array_length ) or only an json object (like jsonb_build_oject) . There are some jsonb columns in the database which contain a mix of both arrays…
Manquer
  • 7,390
  • 8
  • 42
  • 69
22
votes
2 answers

Postgresql JSONB is coming. What to use now? Hstore? JSON? EAV?

After going through the relational DB/NoSQL research debate, I've come to the conclusion that I will be moving forward with PG as my data store. A big part of that decision was the announcement of JSONB coming to 9.4. My question is what should I…
Mike
  • 558
  • 5
  • 13
21
votes
3 answers

JPA support for querying Postgres JSON fields

Is there already support in JPA for dealing with queries on JSON fields like the following : select * from person where (info ->> 'age')::numeric = 40; select * from person where info ->> 'firstName'= 'Cabalo'; I'm using hibernate 5 (behind…
marius_neo
  • 1,535
  • 1
  • 13
  • 28
21
votes
1 answer

Flatten aggregated key/value pairs from a JSONB field?

I am working in Postgres 9.4 with the following table: Column │ Type │ Modifiers ─────────────────┼──────────────────────┼────────────────────── id │ integer │ not null default practice_id │…
Richard
  • 62,943
  • 126
  • 334
  • 542
20
votes
7 answers

How to get only the jsonb of specific keys from postgres?

I'm aware that you can remove keys from a jsonb in postgres using something like this select '{"a": 1, "b": 2, "c":3}'::jsonb -'a'; ?column? ---------- {"b": 2 "c":3} (1 row) Is there a way to only grab specific keys? Like let's say I just want to…
richard_d_sim
  • 793
  • 2
  • 10
  • 23
18
votes
1 answer

Concatenate JSON arrays in PostgreSQL aggregate

I have a table that contains a field of JSON type, containing arrays of data: Column | Type -------------------+--------- id | integer user_id | uuid changes | jsonb exercise_entry_id |…
Dave Vogt
  • 18,600
  • 7
  • 42
  • 54
18
votes
7 answers

postgresql: jsonb update multiple keys in one query

I have the following postgresql rows as JSONB row: {age:26} And I would like to replace it so that that i looks like this: {age: 30, city: "new york city"} How can I do this in postgressql? Someone metioned using jsonb_set(), but I haven't seen…
ApathyBear
  • 9,057
  • 14
  • 56
  • 90
18
votes
2 answers

Query Postgres JSONB where key doesn't exist

Given a set of data. 1 | { 'completed': true } 2 | { 'office_completed': false } Is there a way to query office_completed != 'true' and have it return both records? Or a way to select all records that DON'T have the office_completed key? I can…
andyrue
  • 903
  • 10
  • 24
1 2
3
…
99 100