2,670 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
-1
votes
0
answers
49
views
postgres: High CPU usage with JSONB after adding composite functional indexes
Context
A bit of context. I have a service responsible for handling search queries from our users. The goal of the project is to build a unified storage system that can hold various entities from our ...
0
votes
1
answer
74
views
How do I select a list of all the values of a specific field name in a recursive set of jsonb data in Postgres?
I have a column named layout in a table named pages which contains JSON.
In this case, the JSON actually represents the HTML of a page. Information is infinitely nested, just as an HTML page would be. ...
1
vote
2
answers
83
views
UPDATE using jsonb_delete doesn't remove the target keys
I'm learning the jsonb data type in PostgreSQL. I executed the following statements:
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO my_table (data)
VALUES ('{"...
Advice
2
votes
2
replies
66
views
Postgres JSON or JSONB to store game's character customization data
I'm building the backend for a platform that works similarly to a game. It includes a simple character customization system with a few properties that aren't particularly relevant to the product (...
2
votes
1
answer
80
views
How to use jsonb functions in hibernate 6?
We use Postgres jsonb_agg function in criteria query API like this:
cb.function(
JSONB_AGG_FUNCTION_NAME,
JsonNode.class,
someJoin.get(someField)
...
1
vote
3
answers
114
views
SELECT jsonb data based on specific children values/properties
Assume I have many rows with jsonb data like this in their respective columns:
{
...,
"participants": {
"one_uuid": {
"id": "another_uuid",
&...
2
votes
2
answers
113
views
postgres jsonb array aggregate functions
I have a table with data on user applications. It contains several fields: employee_id text, date date, app_info jsonb.
+-------------+------------+------------+
| employee_id | date | app_info ...
-2
votes
2
answers
125
views
Looking for the operator that extracts the key of one of JSONB objects
Is there an operator that extracts xx from '{"xx":123}'::jsonb?
In my data I always have only one key, so something like #>>'{$.*}' that meets this requirement, perhaps exists.
PS: ...
1
vote
2
answers
429
views
python+asyncpg+PostgreSQL returns jsonb column as a string instead of an object
I'm new to python and just came to a problem: i've set up a simple program to fetch some data from PostgreSQL database. They say that asyncpg library automatically converts JSONB data to Python ...
0
votes
0
answers
76
views
Getting typescript error "No overload matches this call" on Postgres INSERT/UPDATE statement into JSONB column
I'm an experienced programmer (mainly c# and mysql, then moved to javascript/JQuery and now more recently nextjs), but new to nextjs and postgres. I'm trying to insert an object into a JSONB column, ...
0
votes
0
answers
100
views
How to extract JSONB array values from a Supabase Postgres table and insert them into another table in React Native?
I'm working on a React Native app with Supabase as my backend.
My products table has a style_tags column in JSONB array format, for example:
[
{ "tag": "streetwear", "weight&...
1
vote
1
answer
55
views
How to write a query with aggregates of json_array_elements in jOOQ
I have this query:
SELECT organisation_id,
count((j->>'invoiceId')) AS count,
sum((j->>'advanceAmount')::numeric(20,4)) AS sum
FROM runs
LEFT JOIN jsonb_array_elements(...
2
votes
1
answer
71
views
Postgres UPDATE table SET jsonb typed column with javascript var not work for remove an array item
I'm using this package to CRUD in a Postgres database https://github.com/porsager/postgres
This is my query - it works fine to remove a index 0 or n... on a JSON array inside a jsonb column:
UPDATE ...
2
votes
2
answers
121
views
Postgres UPDATE table SET jsonb_set returning invalid input syntax for type json using javascript
I'm using this package to CRUD in a Postgres database
https://github.com/porsager/postgres
This is my query:
POSGRES_SQL`UPDATE pedidos
SET destinatario = jsonb_set(
destinatario,
'...
2
votes
2
answers
79
views
Postgres @> operator confusion
I seem to be having issues with the @> operator:
CREATE TEMP TABLE test_jsonb (id SERIAL, data jsonb);
INSERT INTO test_jsonb (data)
VALUES ('[{"role_id": "1", "user_name&...