select elements from jsonb postgres array

Question

i have this jsonb in my database. I need to show the element that is in state 1 if and only if the previous element is in state 3

I need to make the query that brings me the element that meets the condition.

is posible with postgres ?

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

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

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


    } ]

score 3 · Accepted Answer · answered Sep 22 '20 at 14:28

3

You can unnest the json array to rows in a subquery, then use window function to retrieve the "previous" array element. All the is then left to do is filtering.

Assuming that your json(b!) data is stored in column js of table mytable, you could phrase this as:

select x.obj
from mytable t
cross join lateral (
    select x.obj, lag(obj) over(order by rn) lag_obj
    from jsonb_array_elements(t.js) with ordinality as x(obj, rn)
) x
where (obj ->> 'state')::int = 1 and (lag_obj ->> 'state')::int = 3

Demo on DB Fiddle

answered Sep 22 '20 at 14:28

GMB

216,147
25
84
135

Note that if the json-array itself is not sorted but the order should be derived from `"initial_date"` or `"final_date"` that can easily be done by changing the `(ORDER BY rn)` to e.g `(ORDER BY x.obj->>'initial_date')`. – Marth Sep 22 '20 at 14:41
and if the object with state1 is the first it has no previous object it should show it too [ { "state": 1, "activity": "EJECUCIÓN", }, { "state": 0, "activity": "REVISIÓN", }, { "state": 0, "activity": "RECEPCIÓN", } ] – Dario Paez Sep 22 '20 at 15:23
@DarioPaez: ok so just change the `where` clause to: `where (obj ->> 'state')::int = 1 and ((lag_obj ->> 'state')::int = 3 or lag_obj is null)`. – GMB Sep 22 '20 at 15:31

score 0 · Answer 2 · answered Sep 22 '20 at 14:31

You will need to explode the json to do this:

with invars as (
  select 1 as id, ' [
    {
        "state": 3,
        "activity": "EJECUCIÓN",
        "final_date": "2020-02-24",
        "activity_id": 1,
        "current_days": -7,
        "initial_date": "2020-02-24"

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

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


    } ]'::jsonb as jcol
), expand as (
  select i.id, 
         j.obj->>'state' as this_state,
         lag(j.obj->>'state') 
           over (partition by id
                     order by rn) as last_state,
         j.obj
    from invars i
         cross join lateral 
           jsonb_array_elements(jcol) 
             with ordinality as j(obj, rn)
)
select *
  from expand
 where this_state = '1' 
   and last_state = '3'
;
┌────┬────────────┬────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ id │ this_state │ last_state │                                                                 obj                                                                 │
├────┼────────────┼────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│  1 │ 1          │ 3          │ {"state": 1, "activity": "REVISIÓN", "final_date": "2020-02-25", "activity_id": 2, "current_days": 0, "initial_date": "2020-02-25"} │
└────┴────────────┴────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)

select elements from jsonb postgres array

2 Answers2