All Questions
Tagged with postgresql jsonb
2,375
questions
0
votes
1
answer
21
views
PostgreSQL merge several json list
I read a lot about json combine methods but not found my way.
Have this kind of query
WITH
products AS (
SELECT * FROM configured_products() p
WHERE p.is_active
),
products_countries ...
- 1,684
1
vote
1
answer
25
views
Optomize query for postgresql with index on jsonb array
I have tables:
request(id: int, current_stage_id: int, selection_id: int)
selection(id: int, settings: jsonb)
Settings has array like {id: int, stage_type: int}.
For example: [{id: 1, stage_type: 2},...
- 71
1
vote
2
answers
41
views
How to handle invalid cast operations in JSONB column queries in PostgreSQL?
I have a column called tags of type JSONB in my PostgreSQL table called EMPLOYEE.
There is a use case where I want to allow users to query this JSONB column, i.e., tags.
Users can give inputs like:
...
- 123
0
votes
2
answers
49
views
Doing a bulk update in Vercel Postgres by dynamically building query
I have a simple table in Vercel Postgres:
export const sets = createTable("card_table", {
id: varchar("id").primaryKey(),
info: jsonb("info"),
});
I have populated ...
- 266
0
votes
0
answers
13
views
In Hibernate 5, The Jsonb sql column Serializer/Deserializer using SnakeCase than the camelCase (TypeDef JsonBinaryType)
While saving/retrieving Jsonb Object in a DB. JSON key are saved in SnakeCase than the CamelCase
Entity Class
@Entity
@TypeDef(name = "json", typeClass = JsonBinaryType.class)
public class ...
- 63
0
votes
1
answer
48
views
How to alter column type without exceeding disk space limitations
I have a table with ~20million rows which currently occupies about 30gb. There is an additional 30gb free on this database.
I have one column that was imported as text but needs to be converted to ...
- 1,575
0
votes
0
answers
22
views
How can I add a key/value to a sub-object in a postgres jsonb column
I have a table with a jsonb column containing data like:
{
"key_a": "value",
"key_b": {
"_reference": "reference to remote object 1"
...
- 43
0
votes
0
answers
16
views
How to Convert JSON Result to JSONB in a TypeORM Query Using PostGIS
I am currently working with a PostgreSQL database using PostGIS and TypeORM for a project. My entity includes a column defined as follows:
@Column("geometry", {
transformer: new ...
- 622
0
votes
0
answers
37
views
How do I delete multiple elements from a JSONB structure, using wildcard syntax?
I have a JSONB column with the following values:
{
"collections": {
"drafts": [
{
"availabilities": [],
"languageCodes": [],
...
- 33
0
votes
3
answers
41
views
How do I search within a PostgreSQL jsonb array?
I have a jsonb column that looks like this:
{
"customer_id": "1",
"address": "123 Main St.",
"orders": [{
"order_id": &...
0
votes
0
answers
41
views
PostgreSQL jsonb indexing and searching for arrays and unknown keys
I am working on a PostgreSQL (version 16) database that has two tables, one with data such as id and name and such, and the other table has a jsonb column. I separated the tables because the jsonb ...
1
vote
1
answer
26
views
PostgreSQL GIN index on nested objects in JSONB not working
Disclaimer: I read every "similar question" but still can't get it to work.
I have the following table:
CREATE TABLE test (
id SERIAL PRIMARY KEY,
content JSONB
);
Generating test data
...
- 793
0
votes
2
answers
119
views
Is there any simple way to transform jsonb field into csv? [closed]
I have a model with a jsonb field where responses from a third-party service are stored. The response changes periodically; some fields are added, some disappear. I want to generate a CSV from a ...
- 2,329
0
votes
2
answers
33
views
aggregate jsonb data in Postgres
I have a jsonb field in a database table that holds the following data:
{"access": {
"D2024.06.13": [{"qty": 1, "time": "12:05"}, {"qty": ...
- 17
0
votes
1
answer
53
views
How to update huge amount of jsonb's in DB
I have to update >100GB of data of jsonb values in a postgres DB that have the following structure:
{
"attributes": [
{
"attribute": "foobar",
"rules&...
- 3