Questions tagged [jsonb-array-elements]
16 questions
1
vote
2 answers
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",
"name": "target1"
},
"0f43e1fe-132e-464b-8284-4a9947a70c1c": {
…
Nikowhy
- 141
- 2
- 10
1
vote
2 answers
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:51.400124Z"}, {"start": "2022-10-28T08:27:13.682084Z"}]
6
[{"start":…
1
vote
0 answers
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
["hex_address", 101,…
Bruce
- 11
- 2
1
vote
2 answers
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", "Value": "10"}]" |
| 2 | "[{"Step": "step1", "Value": "999"}]"…
Gautam
- 238
- 6
- 17
1
vote
2 answers
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.
example for 2 tables
table1:
id: 1
data:
[
{
…
Tzach
- 13
- 3
1
vote
2 answers
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)| …
Tarun Annapareddy
- 33
- 4
0
votes
1 answer
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 notes key which will be a text array. If it doesn't exist, an UPDATE query should create it…
user21823446
- 11
- 3
0
votes
1 answer
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:
[
{
"activity": "Gym",
"cardio": false,
"strength":…
Schoxy
- 1
0
votes
1 answer
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 list of **name**s of tags from tag table => should be converted
to list of…
Fahimeh Rahmatipoor
- 93
- 1
- 10
0
votes
1 answer
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
)
…
Luke
- 1
- 2
0
votes
2 answers
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 others it will come as an object.
for example, the…
adbdkb
- 1,897
- 6
- 37
- 66
0
votes
1 answer
Retrieve specific value from a jsonb object postgres 11
I have the following jsonb column in my table requests called…
Kingsley Simon
- 2,090
- 5
- 38
- 84
0
votes
1 answer
Jsonb array elements
How to extract data from a jsonb column
Data Table
Id
Result
1001
{"Green": {"value": "250.3", "reading": "250.3"}, "MbSampleType": {"value": "Water"}}
1002
{"Green": {"value": "0", "reading": "0"}, "Yellow": {"value": "560", "reading":…
Satish
- 5
- 3
0
votes
1 answer
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": "ceeb364e-bb88-4f41-9c56-9e5f4d0bc1fb",
"value": None
},
...
]'::JSONB
And I want to…
Prosto_Oleg
- 322
- 3
- 13
0
votes
1 answer
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 | code…
