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"


    } ]

Answer 1

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