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
1 answer

Printing an information nested in PostgreSQL json data

The following PostgreSQL query produces an output in json format. SELECT exampleColumn::jsonb FROM public."MyTable" The output is as follows: [ { "testSettings":{ "www.test1.com":{ "IpAddress":[ { …
OmerFaruk
  • 48
  • 5
0
votes
1 answer

PostgreSQL: JSONB; get the key name by path

I have a requirement, on given path, I need to get the key name of JSONB, for example: '{ "name": "John Doe", "age": 30, "address": { "street": "123 Main St", "city": "Anytown", "state": "CA", "zip": "12345", "data": {"a":…
Shay Zambrovski
  • 401
  • 5
  • 21
0
votes
0 answers

How should I operated the field in PostgreSQL column as jsonb type? (jsonb_set)

I have a column which had json schema value { "red": false, -> would like switch to true "blue": false, "yellow": true } how should I switch the boolean value under the specific filed ? (assume I do not know the original value in this…
Obito Kang
  • 1
  • 1
0
votes
1 answer

In postgres updating a jsonb column, how do you create OR append an array in one query?

I have a table with some basic jsonb, for this example lets call it transactions CREATE TABLE transactions ( transaction_id text NOT NULL, body jsonb, ); So inside the body I have some data we can ignore but it's an object something…
mdubbpro
  • 1
  • 1
0
votes
2 answers

How to return only subset of elements from a JSONB field in postgresql

I have a table in Postgres with the structure bellow. id name objects(jsonb) 1 first [{"name":"a", value:"1", "param":{}}, {"name":"b", "value":"55", "param":{}}, {"name":"c", "value":"234", "param":{}}] 2 second [{"name":"b",…
Daniel
  • 341
  • 6
  • 24
0
votes
0 answers

rpc function error in Supabase with jsonb - Invalid input syntax for type json

I have a Supabase table which stores data from a Discord guild. Data includes a list of current text channels within the guild the bot is in. The channels are currently stored as a JSONb under a guild_channels column, with each object within the…
OrcaDrive
  • 17
  • 5
0
votes
1 answer

Postgres JSONB not working with Upgrade to SpringBoot Version 3.0

I have a Kotlin SpringBoot project. It's a relatively simple API that persists data to a Postgres JsonB database. I am using the @TypeDef annotation on my entity class, but after upgrading to SpringBoot Version 3.0 with…
Johnny Alpha
  • 758
  • 1
  • 8
  • 35
0
votes
0 answers

Query jsonb array column

I'm not having any luck querying a jsonb[] column (attributes). I couldn't find anything in the Postgresql docs about arrays of jsonb My data in the column is a bunch of objects in an array like this: 1 - [{ "trait_type": "color", "value": "blue"…
jinsley8
  • 81
  • 5
0
votes
1 answer

How to use PSQL to extract data from an object (inside an array inside an object inside an array)

This is data that is currently sitting in a single cell (e.g. inside warehouse table in warehouse_data column) in our database (I'm unable to change the structure/DB design so would need to work with this), how would I be able to select the name of…
MB360
  • 21
  • 5
0
votes
2 answers

Aggregate jsonb map with string array as value in postgresql

I have postgresql table with a jsonb column containing maps with strings as keys and string arrays as values. I want to aggregate all the maps into a single jsonb map. There should be no duplicate values in string array. How can I do this in…
Abhay Gupta
  • 786
  • 12
  • 30
0
votes
0 answers

How to format CSV file (with varying columns) for upload into Rails App + Postgresql

I have been playing around with uploading standard CSV files to a Rails App and inserting the records into the database. However, I am looking to expand beyond a fixed set of attributes (say for example description/cost/price) and add other…
spitey
  • 31
  • 2
0
votes
1 answer

Querying values jsonB in Postgresql

I have a table with ProductID (int) and ProductGroups jsonb. The ProductGroups just have values in the json rather than tag names. I want to be able to query the following data to get ProductID where ProductGroup contains 69. ProductID …
Robert Gepfert
  • 1
  • 1
0
votes
1 answer

Postgres jsonb_array_elements() returns "cannot extract elements from a scalar", even when given a valid JSON array?

I have a table in postgres called day, which contains a jsonb column called plan_activities. I have a day record with day.id = 18 and plan_activities contains the following JSON: [ { "activity": "Gym", "cardio": false, "strength":…
Schoxy
  • 1
0
votes
1 answer

JSONB - query Postgres

I have following table with data cars.details (country_id, city_id, value) 1. ('JAP', '53', ('{"model":"Toyota", "weight":"900", "description":"Family Car.", "tires":"3"}')) cards.additions (country_id, city_id, value) 1. ('JAP', '53',…
Vasek
  • 3
  • 2
0
votes
0 answers

Postgresql jsonb doesn't work with Spring Data JPA

I tried to save into a jsonb column but I didn't find any solution of my problem. Postgresql 15. This is the mapping class @Getter @Setter @NoArgsConstructor @Entity(name = "template") @Table(name = "template") public class Template { @Id …
adam pomari
  • 11
  • 2
1 2 3
…
99
100