27 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
0
votes
1
answer
86
views
Exclude from the selection the values that are in the array [closed]
I have two tables:
my_temp:
employee_id
date
start_granula
end_granula
count_seconds
2223eb0f0d0x
2025年08月22日
07:40:00
07:50:00
625
2223eb0f0d0x
2025年08月22日
08:10:00
08:20:00
513
2223eb0f0d0x
2025年08月22日
...
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(...
1
vote
2
answers
183
views
Check if an element exists in jsonb field
I want to know if json array contains a particular element or not. I am trying the below query for getting the result.
But it is not working as expected.
with cte as (
select jsonb_array_elements(...
1
vote
2
answers
71
views
Postgres, searching with ILIKE operator from json array works different, than from regular table
In our DB we have tables, that have 6-8 M records, this data is static and in order of performance optimization we decided to store some data from this tables in separate table, and for predefined ...
0
votes
1
answer
546
views
How do I get all column values in case of nested json values in column for sql?
Here is a sample of the data that I am working with:
id | col1 | col2
1 | Name1 | {'spec_details': {'spec_values': [{'name':'A','value':2}, {'name': 'B', 'value': 5}, {'name': 'C', '...
0
votes
0
answers
33
views
Extract int value from json and multiply by specific value to sum in custom column in PostgreSQL
Good day
please, help with this.
I have table "tickets" with column printer_catalog:
records from 1st row of column printer_catalog
{"Refueling::HP 1005 (65gr)::2 pcs","...
2
votes
1
answer
169
views
How to convert elements to snake_case format in jsonb column array [Postgresql]
I have a database table(call it table) with jsonb column(lets say column). Column contains bellow data:
{
"country": "GB",
"channel": "mobile",
"...
0
votes
1
answer
43
views
Join sql table on key from jsonb array column in one table to field on another table
I have users table which has a cart column of type jsonb array, for example:
[{"product_id": 1, "qty": 2},
{"product_id": 2, "qty": 3}]
and products table ...
0
votes
1
answer
62
views
Repeated values appear in array after updating jsonb column
I have this table:
CREATE TABLE driver_orders (
id SERIAL PRIMARY KEY,
driver_id INTEGER NOT NULL,
seats JSONB NOT NULL
)
I store this value in that table:
id
driver_id
seats
1
10029
[{"id&...
0
votes
3
answers
423
views
Problem with JSONB functions retrieving arrays
In a table called temporay_data with a data field called temporary_data too, filled with this JSON structure
{
"FormPayment": {
"student": [
{
&...
0
votes
1
answer
83
views
Invalid field error on Sequelize bulkInsert method for Array of nested JSONB fields
I have a metadata column in my sequelize Notifications model
metadata: { type: DataTypes.ARRAY(DataTypes.JSONB) }
I want to do a bulkInsert for the notifications table using a seeder file but each ...
1
vote
2
answers
133
views
Select jsonb column with a subset of nested array based on id
I have a jsonb column in the following format:
{
"targets": {
"01d7de90-57fd-4c4f-b7c9-3b956762fe25": {
"id": "01d7de90-57fd-4c4f-b7c9-3b956762fe25",...
0
votes
1
answer
47
views
postgresql: Adding/updating a key with array value to json column
I have a column "data" with datatype json that may be empty { } or may contain some keys already:
{ "category": "alpha", "verified": true }
I want to have a ...
0
votes
1
answer
2k
views
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:
[
{
"...
0
votes
1
answer
110
views
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<String> list of **name**s of tags from tag ...