Edit fields of a jsonb array in postgresql

Question

I have the following jsonb in db:

[
    {
        "state": 2,
        "activity": "EJECUCIÓN",
        "final_date": "2020-02-24",
        "activity_id": 1,
        "current_days": -7,
        "initial_date": "2020-02-24",

    },
    {
        "state": 2,
        "activity": "REVISIÓN",
        "final_date": "2020-02-25",
        "activity_id": 2,
        "current_days": 0,
        "initial_date": "2020-02-25",

    },
    {
        "state": 2,    
        "activity": "RECEPCIÓN",
        "final_date": "2020-02-27",
        "activity_id": 4,
        "current_days": 0,
        "initial_date": "2020-02-27"


    } ]

I run the following query to update the current_days field:

WITH activity_state     as  ( SELECT taex_id,('{'||index-1||',current_days}')::text[] as pathe ,
        ((task_activity->>'final_date')::date - current_date) as current_days,
        task_activity->'state' as state,
        task_activity->>'final_date' as final_date,
        task_activity->>'current_days' as curren   FROM task_executions,jsonb_array_elements(taex_activitygraph) with ordinality arr(task_activity,index)   WHERE task_activity->>'state' = '2'  )  

update task_executions SET  taex_activitygraph = jsonb_set(taex_activitygraph,activity_state.pathe,to_jsonb(current_days),true) FROM activity_state  WHERE task_executions.taex_id = activity_state.taex_id  AND activity_state.state = '2'

But that query only updates me the first element of the JSON array that exists the others do not undergo changes although in the first part of the query.

( SELECT taex_id,('{'||index-1||',current_days}')::text[] as pathe ,
        ((task_activity->>'final_date')::date - current_date) as current_days,
        task_activity->'state' as state,
        task_activity->>'final_date' as final_date,
        task_activity->>'current_days' as curren   FROM task_executions,jsonb_array_elements(taex_activitygraph) with ordinality arr(task_activity,index)   WHERE task_activity->>'state' = '2'  )

It brings me all the elements of the array that should be updated but the second part that is where it is supposed to update them:

update task_executions SET  taex_activitygraph = jsonb_set(taex_activitygraph,activity_state.pathe,to_jsonb(current_days),true) FROM activity_state  WHERE task_executions.taex_id = activity_state.taex_id  AND activity_state.state = '2'

Just update me the first item.

filiprem · Accepted Answer · 2020-03-09 13:40:30Z

2

Assuming this structure and data:

postgres=# \d task_executions 
               Table "public.task_executions"
       Column       | Type  | Collation | Nullable | Default 
--------------------+-------+-----------+----------+---------
 task_activitygraph | jsonb |           |          | 

postgres=# SELECT jsonb_pretty(task_activitygraph) FROM task_executions ;
             jsonb_pretty             
--------------------------------------
 [                                   +
     {                               +
         "state": 2,                 +
         "activity": "EJECUCIÓN",    +
         "final_date": "2020-02-24", +
         "activity_id": 1,           +
         "current_days": -7,         +
         "initial_date": "2020-02-24"+
     },                              +
     {                               +
         "state": 2,                 +
         "activity": "REVISIÓN",     +
         "final_date": "2020-02-25", +
         "activity_id": 2,           +
         "current_days": 0,          +
         "initial_date": "2020-02-25"+
     }                               +
 ]
(1 row)

... this UPDATE should work:

postgres=# UPDATE task_executions
SET task_activitygraph = (
  SELECT jsonb_agg(
    CASE WHEN elem->>'state' = '2'
    THEN 
      jsonb_set(
        elem,
        '{current_days}',
        to_jsonb((elem->>'final_date')::date - current_date)
      )
    ELSE
      elem
    END
  )
  FROM jsonb_array_elements(task_activitygraph) AS a(elem)
);
UPDATE 1

Documentation: https://www.postgresql.org/docs/9.5/functions-json.html

Side note: In transactional databases (where you have many concurrent clients, and processing speed and storage efficiency matters), and if your objects have fixed structure, DO NOT STORE your data as JSON. Use relational data model instead.

edited Mar 9, 2020 at 13:40

answered Mar 4, 2020 at 22:18

filiprem

7,2141 gold badge34 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dario Paez Over a year ago

Thank you what you answered works but .... WHERE final_date < current_date to_jsonb((elem->>'final_date')::date - current_date) WHERE (elem->>'initial_date') <= current_date AND (elem->>'final_date' )>= current_date to_jsonb((elem->>'initial_date')::date - current_date) in theory that towards the condition in my query

filiprem Over a year ago

@DarioPaez excuse me, not clear what you mean, could you update your original question with specific what you want to achieve? (also table structure would be helpful)

Dario Paez Over a year ago

in the update you send you don't consider the activity_state condition.

filiprem Over a year ago

@DarioPaez got it, updated. (with CASE statement inside jsonb_agg)

Collectives™ on Stack Overflow

Edit fields of a jsonb array in postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related