27 questions
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
52
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,791
1
vote
2
answers
168
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(...
- 49
1
vote
2
answers
70
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 ...
- 447
0
votes
1
answer
540
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', '...
- 425
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","...
- 98
2
votes
1
answer
165
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",
"...
- 55
0
votes
1
answer
39
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
58
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&...
- 5,394
0
votes
3
answers
417
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
76
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 ...
- 434
1
vote
2
answers
132
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",...
- 151
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:
[
{
"...
- 1
0
votes
1
answer
96
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 ...
0
votes
1
answer
219
views
I am trying to create a trigger for an update on jsonb column in Postgresql 13.7
CREATE OR REPLACE FUNCTION update()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
with name_array as (select jsonb_array_elements(inputs #> '{lists}') AS lists
from random.model
...
- 1
1
vote
2
answers
406
views
Transform data from a jsonb column into a table in postgresql
I have a table which stores timestamps but they're in a jsonb array column like this:
id
cycles
5
[{"end": "2022-10-18T18:31:34.529667Z", "start": "2022-10-05T19:01:...
1
vote
0
answers
46
views
query the jsonb array data by specific field condition with OR and ORDER BY constraint really slow
I have a postgresql table named events
Name
Data Type
id
character varying
idx
integer
module
character varying
method
character varying
idx
integer
block_height
integer
data
jsonb
where data is like
[...
- 11
0
votes
2
answers
574
views
Convert an object to array of size 1 in PostgreSQL jsonb and transform the json with nested arrays to rows
I have a two part question
We have a PostgreSQL table with a jsonb column. The values in jsonb are valid jsons, but they are such that for some rows a node would come in as an array whereas for ...
- 2,259
0
votes
1
answer
442
views
Retrieve specific value from a jsonb object postgres 11
I have the following jsonb column in my table requests called tasks
+------------------------------------------------------------------------------------------------------------------------------------...
- 2,230
1
vote
2
answers
1k
views
Add new Property in Jsonb column by calculating some of its properties Using Postgressql
I have a jsonb column such that:
| id | fee |
|----------|----------------------------------------|
| 1 | "[{"Step": "step1", &...
- 238
0
votes
1
answer
226
views
Jsonb array elements
How to extract data from a jsonb column
Data Table
Id
Result
1001
{"Green": {"value": "250.3", "reading": "250.3"}, "MbSampleType": {"...
- 5
1
vote
2
answers
181
views
Postgresql join on jsonb array
I'm new to JSONB and I am wondering, if the following would be possible with a single query:
I have a lot of tables that look like this:
ID (INT) | members (JSONB)
all the tables has only one row.
...
- 13
0
votes
1
answer
956
views
malformed array literal when convetring jsonb array of jsonb items to postgres array of jsonb by jsonb_array_elements
I have jsonb-array:
element_values := '[
{
"element_id": "a7993f3d-9256-4354-a147-5b9d18d7812b",
"value": true
},
{
"element_id": "...
- 436
1
vote
2
answers
159
views
Query to search over array elements inside jsonb PSQL
I have a JSON node on which I have to write a PSQL query, My table schema name(String),tagValues(jsonb). Example tagValue data is given below
Name_TagsTable
uid | name(String)| ...
0
votes
1
answer
156
views
psycopg2 - Append a value to a jsonarray in a json column at a specific key
How to add an element in a jsonarray object stored in a dict to a specific key and row of this postresql table with psycopg2 python lib :
To illustrate, go from this :
| json_column ...
0
votes
1
answer
144
views
PSQL - Query to transpose JSONB element
I have a jsonb object like this:
{
"applicants": [
{
"last_name": "ss",
"first_name": "ss",
"age": 31
},
{
...
- 2,172