Querying for an array object in Postgres jsonb column

Question

I have a Postgres table with 2 columns "nodes" & "timestamp".The "nodes" column is of type jsonb & is an array of objects of the following format:

[
    {
        "addr": {},
        "node_number": "1",
        "primary": false
    },
    {
        "addr": {},
        "node_number": "2",
        "primary": true
    },
]

I want to find the object in this array that has "primary":true in the most recent row. If the above was the latest row, the result should be:

{
    "addr": { },
    "node_number": "2",
    "primary": true
}

I have tried:

SELECT(nodes -> 0) FROM table WHERE nodes @> '[{"primary": true}]'
order by timestamp desc
limit 1;

which gives the object at index 0 in the array not the desired object that has "primary": true.

How can I implement the query ?

Answer 1

Use jsonb_array_elements() in a lateral join:

select elem
from my_table 
cross join jsonb_array_elements(nodes) as elem
where (elem->>'primary')::boolean

                       elem                        
---------------------------------------------------
 {"addr": {}, "primary": true, "node_number": "2"}
(1 row)