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 ?

klin · Accepted Answer · 2019-01-07 21:33:45Z

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)

answered Jan 7, 2019 at 21:33

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

saltnpepa Over a year ago

I cant upvote yet but this answer works - so thanks!

Collectives™ on Stack Overflow

Querying for an array object in Postgres jsonb column

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related