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

How to filter rows based on jsonb column array values on jpql query with spring data

I develop an application with spring boot, spring data and postgresql(v.12.3). I have a table that has jsonb column. This jsonb column has array parameter for each row like: { "comboItems": [ { "id":…
séan35
  • 966
  • 2
  • 13
  • 37
0
votes
1 answer

How do I modify jsonb in place, adding keys and moving values to aid querying later

I've got the following hypothetical data items in my postgres db, table: user_tracking id: 210 date: 2022-02-06 17:05:44.000 +0600 item_json: {"MouseDown": "202,2525"} id: 212 date: 2022-02-06 17:05:46.000 +0600 item_json: {"MouseUp":…
Rafe
  • 512
  • 1
  • 4
  • 15
0
votes
1 answer

How to filter in JSONb column in SQLAlchemy ORM

I've the following model: from sqlalchemy.dialects.postgresql import JSONB class User(Model): __tablename__ = "users" params = Column(JSONB) # it contains array of objects Example of User.params: [{"a": "111", "b": "222"}, {"a":…
Mark Mishyn
  • 3,921
  • 2
  • 28
  • 30
0
votes
1 answer

Rails not permitting payload from JSON schema

I am trying to figure out why my rails app is not permitting my attributes and have run out of ideas. I have products in my database (model Product) and they have a collection of standard attributes that apply to all products. I have an additional…
dukehenry
  • 21
  • 1
  • 4
0
votes
1 answer

select a single json element based on a property value

I have the following content in a jsonb column: [ { "Value": "ABC", "PropertyTypeId": 1 }, { "Value": "CDE", "PropertyTypeId": 2 }, { "Value": "FGE", "PropertyTypeId": 3 } ] And I want to get the value of the…
r-work
  • 3
  • 2
0
votes
0 answers

This method requires a body instead of a semicolon, Java Quarkus post request, and interface does not have the @RegisterRestClient

Trying to send a post request in quarkus simple without any configs, as referred in the official docs here And referred in this StackOverflow answer here as well. I just did added the extension quarkus-rest-client-reactive-jsonb and this straight…
MansouriAla
  • 177
  • 1
  • 1
  • 11
0
votes
0 answers

Query field inside array of json / json b with sequelize

So imagine I have a model like the following Model.init( { id: { type: DataTypes.BIGINT, primaryKey: true, autoIncrement: true }, services: { type: DataTypes.ARRAY(DataTypes.JSONB) }, }, { timestamps: true, sequelize:…
Juan David Ruiz Cohen
  • 13
  • 1
  • 4
0
votes
2 answers

SQL - update info in a specified column for multiple rows

I have an SQL table laid out like so Column Type Modifiers uuid uuid not null name character varying type character varying info jsonb created bigint Inside the info column is numerous things such as {"id":…
databasefoe
  • 39
  • 5
0
votes
1 answer

Postgres jsonb query array of objects

I am trying to a do a query to Postgres 15 to return the room id, querying by 2 userid. Example: I want to get the roomid, querying by the userid:1 and userid:2 then get his userid, I don't realize how to do it. SELECT * FROM room WHERE users @>…
JAZ CODM
  • 3
  • 1
0
votes
0 answers

Create custom Postgres JSONB operator for type checking

I'd like to create a custom JSONB operator for Postgres ?@ that's similar to the ? operator described below, but instead of returning whether the value exists, returns the type of the key like jsonb_typeof. How do I do that? jsonb ? text →…
pir
  • 5,513
  • 12
  • 63
  • 101
0
votes
0 answers

Postgres DB can't find all rows that fall under conditions

I have a table, for example create table test ( id bigint, test jsonb ); I inserted two rows INSERT INTO test (id, test) VALUES (1, '{"a": 10, "b": 20}'); INSERT INTO test (id, test) VALUES (2, '{"a": 9, "b": 20}'); Then I executed a…
Vladislav Plakhov
  • 1
0
votes
1 answer

Talend, PostgreSQL error: "Expecting property name enclosed in double quotes"

I am using Talend API tester to send a POST request to my FastAPI backend, and a PostgreSQL database. Please note that the table has a JSONB column. I get the error message; "msg": "Expecting property name enclosed in double quotes: line 10 column 3…
Uwem Uke
  • 19
  • 7
0
votes
0 answers

jsonb will not be updated if I add a field

With this code, Hibernate does not execute an update statement MyEntitity entity; entity.getJSONB().put("Field", 14f); //returns Map session.merge(entity); this works: MyEntitity…
user2071938
  • 2,055
  • 6
  • 28
  • 60
0
votes
1 answer

PostgreSQL | JSONB: get the first path value

Given a JSON input: { "name": "John Doe", "age": 30, "address": { "street": "123 Main St", "city": "Anytown", "state": "CA" }, "phone": 1234567 } Is it possible (if so and how? I could not managed to do…
Shay Zambrovski
  • 401
  • 5
  • 21
0
votes
1 answer

prevent Hibernate from using table aliases in subselect via @Formula

I have this definition in my Entity @Formula(value = "(select coalesce(sum(v.value::float),0) from jsonb_each(urlaub_korrekturen) as \"v\")") private Float urlaubKorrektur; Hibernate generates this select query which would work perfectly if I…
user2071938
  • 2,055
  • 6
  • 28
  • 60
1 2 3
…
99 100