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
0
votes
0 answers

jsonb field indexing

I am trying to optimize the following sql query select * from begin_transaction where ("group"->>'id')::bigint = '5' without using additional indexing i get this Gather (cost=1000.00..91957.50 rows=4179 width=750) (actual time=0.158..218.972…
mr mcwolf
  • 2,574
  • 2
  • 14
  • 27
0
votes
1 answer

Querying an JSONB array of objects in Rails 7

I'm on Ruby On Rails 7 I have a class ActiveRecord class called Thread. There are 2 records : id: 1, subject: "Hello", participants: [{"name"=>"guillaume", "email"=>"guillaume@example.com"}, {"name"=>"Fabien",…
Guillaume
  • 1,437
  • 2
  • 15
  • 17
0
votes
1 answer

Postgres searching with JSONB field for keys and/or values of json object

I am looking for find how to correctly query a Postgres JSONB field. Suppose I have a JSON object like so {"key1": ["value1", "value2"], "key2": ["value1", "value3"]} And I'm storing it in the field 'data', I can query for the existence of the…
RobL
  • 37
  • 1
  • 5
0
votes
1 answer

Jsonb array of objects update

So this is my jsonb array of objects. Column is called bids in my db. bids column [ { "id": "1", "size": "5.5Y", "price": 180 }, { "id": "f0d1d36a-f6af-409e-968e-54c1dc104566", "size":…
Perke
  • 7
  • 3
0
votes
2 answers

In Postgres, how can I efficiently filter using the inner numbers of this jsonb structure?

So I work with Postgres SQL, and I have a jsonb column with the following structure: { "Store1":[ { "price":5.99, "seller":"seller" }, { "price":56.43, "seller":"seller" } ], …
Yaniv Bin
  • 9
  • 4
0
votes
0 answers

How do I use an array of integers to query a jsonb column?

Ruby on Rails with postgres database. I have an EventOccurrence model that has a jsonb column named 'details'. I have three EventOccurrence records, like this: EventOccurrence id: 472, event_type: "Event", event_id: 19, details: …
jvillian
  • 19,953
  • 5
  • 31
  • 44
0
votes
1 answer

Record changes in a different table

I have successfully set up a history table according to this tutorial: https://www.cybertec-postgresql.com/en/tracking-changes-in-postgresql/ My problem is that this function saves both the whole new record and the whole old record as jsons. How can…
dersu
  • 189
  • 1
  • 7
0
votes
1 answer

postgresql jsonb update List of string value based on existing values searched in other table

I have two tables like these in postgres db : TABLE tag ( id number, name nvarchar ); TABLE article ( id number, tags jsonb // List list of **name**s of tags from tag table => should be converted to list of…
Fahimeh Rahmatipoor
  • 93
  • 1
  • 10
0
votes
1 answer

How to use an index when using jsonb_array_elements in Postgres

I have the next table structure: create table public.listings (id varchar(255) not null, data jsonb not null); And the next indexes: create index listings_data_index on public.listings using gin(data jsonb_ops); create unique index listings_id_index…
Nicolás González
  • 121
  • 12
0
votes
0 answers

How can I use JsonParser (javax.json.stream.JsonParser) twice

I need to use a JsonParser twice, once to validate the format of my JsonStream by the json schema given..., then I need to contruct my object 'Product'. The problem is that if I use the parser once, I cannot re-use it a second time. It's like it…
Najlae Cherkaoui
  • 3
  • 1
0
votes
2 answers

Problem with PostgreSQL, JSONB, nested queries and database indices

currently I'm struggeling with PostgreSQL 11, JSONB and database indices. We have a customers table (shortened): create table public.customers ( id uuid not null, created timestamp without time zone, lastmodified timestamp without…
greenwizard
  • 1
  • 1
0
votes
0 answers

Is it possible to preload a nested object into an ActiveRecord relation?

I'm trying to parse a snapshot of an object, which is stored in a Postgres database in a jsonb column, into an object of the snapshotted model. The json is multi-leveled, meaning that it has the fields of the object AND the fields of all child…
Morragan
  • 1
  • 1
  • 2
0
votes
1 answer

Postgres JSONB, query first appearance of inner field

Trying to query product_id and inner JSONB value "quantity_on_hand" with "unit:one". Below is example table Products table | product_id | data | | -------- | -------------| | 00445 | {"storage"...}| - rest of data specified right…
Kirill Sazonov
  • 5
  • 3
0
votes
0 answers

Django case insensitive search in multilevel jsonb field using ORM methods

here is my sample jsonb field: { "name": "XXXXX", "duedate": "Wed Aug 31 2022 17:23:13 GMT+0530", "structure": { "sections": [ { "id": "0", "temp_id": 9, "expanded": true, "requests": [ { …
Sreeraj mp
  • 1
  • 1
0
votes
1 answer

How to reference a jsonb coulmn value from a value map in postgres

I want to be able to reference the error table for msg and description based on the err_id on the results table for err_map jsonb column, I'd also want to be able relate which error occurred against which column whether the independent columns c1,c2…
Saurabh Sharma
  • 489
  • 5
  • 20
1 2 3
…
99
100