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
18
votes
2 answers

add index on jsonb field

I need to search over the values of an array key of jsonb field in Postgres. field: {'array_key' : [1, 2, 3, 4]} Is it possible to add index on array_key or is there any optimized method to search over the values ? search query will be something…
Raza
  • 2,320
  • 2
  • 22
  • 32
17
votes
6 answers

Remove multiple keys from jsonb column in one statement

I'm trying to remove multiple keys from a jsonb column. I've managed to get it to delete a single key with the following statement: UPDATE table_a SET data_column = data_column #- '{attr_1}' WHERE type = 'type_a' I understand thats a path so I…
Sam Esmail
  • 333
  • 1
  • 3
  • 14
17
votes
2 answers

How can I do less than, greater than in JSON Postgres fields?

If I have some json: id = 1, json = {'key':95} id = 2, json = {'key':90} id = 3, json = {'key':50} Is there a way I can use Postgres fields to query for key greater than >= 90?
Kamilski81
  • 14,409
  • 33
  • 108
  • 161
17
votes
4 answers

Remove jsonb array element by value

I did figure out how to remove a value from an array for a single record, but how to do it for many of them. The problem is in the way how I use the subquery. As it has to return only single element. Maybe my approach is wrong. Given input:…
simpleman
  • 205
  • 1
  • 2
  • 7
17
votes
3 answers

Select value of jsonb column in PostgreSQL

I have a table 'Documents' which has a column 'Tags' with 'jsonb' datatype. Sample data in Tags column [{"Tag": "Social Media"}, {"Tag": "Adobe Creative"}] [{"Tag": "Interactive"}] [{"Tag": "Web 2.0"}, {"Tag": "Adobe Creative"},{"Tag":…
Ajoe
  • 1,397
  • 4
  • 19
  • 48
16
votes
2 answers

How to use `jsonb_set` on column with null values

I am using Postgres 9.6 and I have a JSONB column in which some rows have NULL value and some have dict values like {"notify": false}. I want to update the column values with more dictionary key/value pairs. UPDATE accounts SET notifications = …
iffi
  • 258
  • 3
  • 7
16
votes
5 answers

Merging JSONB values in PostgreSQL?

Using the || operator yields the following result: select '{"a":{"b":2}}'::jsonb || '{"a":{"c":3}}'::jsonb ; ?column? ----------------- {"a": {"c": 3}} (1 row) I would like to be able to do achieve the following result (?? just a …
bguiz
  • 27,371
  • 47
  • 154
  • 243
16
votes
2 answers

SELECT values which are not null from JSONB field in Postgres

I am unable to select non-null values from a property inside a JSONB field in Postgres 9.5 SELECT data->>'property' FROM mytable WHERE data->>'property' IS NOT NULL; I also tried using NOTNULL. I receive error 42883 when I run either of these.…
sheldonkreger
  • 858
  • 1
  • 9
  • 25
15
votes
2 answers

How to filter jsonb array elements

I have a table with a jsonb column which contains an array of objects: create table my_table( id int primary key, phones jsonb); The objects consist of phone numbers and statuses. insert into my_table values (1, '[ { "phone":…
lalol
  • 163
  • 1
  • 6
15
votes
4 answers

Rails jsonb - Prevent JSON keys from reordering when jsonb is saved to Postgresql database

I have a column amount_splits that I need to save my JSON to in the key order I've specified. How do I prevent Rails / Postgres jsonb from auto sorting my JSON keys when I save it to the database? (for creating or updating) It looks like it's trying…
skplunkerin
  • 2,123
  • 5
  • 28
  • 40
15
votes
2 answers

Globally replace in Postgres JSONB field

I need to globally replace a particular string that occurs multiple places in a nested JSON structure, thats stored as jsonb in a postgres table. For example: { "location": "tmp/config", "alternate_location": { "name": "config", …
crowhoplaminar
  • 213
  • 1
  • 2
  • 8
15
votes
1 answer

Best PostgreSQL datatype for storing key-value maps?

I'd like to store a simple map of key-value strings as a field in my PostgreSQL table. I intend to treat the map as a whole; i.e, always select the entire map, and never query by its keys nor values. I've read articles comparing between hstore, json…
Eyal Roth
  • 3,895
  • 6
  • 34
  • 45
15
votes
3 answers

How to use to_jsonb as row_to_jsonb? Where the details about "how much"?

I was testing some queries at pg9.4 in "JSON mode", and now I am checking if pg9.5 will bring all same JSONB functionality... But there are no row_to_jsonb() function (!). (why it is not orthogonal instruction set in the basic parameters?) The…
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
14
votes
2 answers

PostgreSQL throws "column is of type jsonb but expression is of type bytea" with JPA and Hibernate

This is my entity class that is mapped to a table in postgres (9.4) I am trying to store metadata as jsonb type in the database @Entity @Table(name = “room_categories”) @TypeDef(name = “jsonb”, typeClass = JsonBinaryType.class) public class…
Arihant Jain
  • 141
  • 1
  • 1
  • 3
14
votes
1 answer

Postgresql, retrieve value for specific key from json array

I have Postgres JSONB array of objects, looking like this : '[ { "skillId": "1", "skillLevel": 42 }, { "skillId": "2", "skillLevel": 41 } ]' This JSONB is a function argument. What is the most efficient way to retrieve…
Aliaksei Stadnik
  • 1,692
  • 3
  • 15
  • 32
1 2 3
…
99 100