Skip to content
Advertisement

Postgres query nested JSONB

I have a JSONB column containing list of objects.>
Here’s the table schema:

Sample Data

1.

2.

3.

I need to find the count of the transitions of tracker_id from position: 1 to position: 2
Here, the output will be 2, since tracker_id 4 and 5 changed their position from 1 to 2.

Note
The transition should be in ascending order depending on the timestamp
The position change need not to be in the consecutive records.
I’m using timescaledb extension

So far I’ve tried querying the objects in the list od individual record, but I’m not sure how to merge the list objects of each record and query them.

What would be the query for this? Should I write down a stored procedure instead?

Advertisement

Answer

I don’t use timescaledb extension so I would choose pure SQL solution based on unnesting json:

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement