CODEWITHSUNDEEP
sqlpostgresqltimescaledb
saintlyzero
saintlyzero

Reputation: 1852

Postgres query nested JSONB

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

column Name | Datatype
---------------------
timestamp   | timestamp
  data      | JSONB

Sample Data

1.

timestamp : 2020-02-02 19:01:21.571429+00

data : [
  {
    "tracker_id": "5",
    "position": 1
  },
  {
    "tracker_id": "11",
    "position": 2
  },
  {
    "tracker_id": "4",
    "position": 1
  }
]

2.

timestamp : 2020-02-02 19:01:23.571429+00

data : [
  {
    "tracker_id": "7",
    "position": 3
  },
  {
    "tracker_id": "4",
    "position": 2
  }
]

3.

timestamp : 2020-02-02 19:02:23.571429+00

data : [
  {
    "tracker_id": "5",
    "position": 2
  },
  {
    "tracker_id": "4",
    "position": 1
  }
]

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?

Upvotes: 0

Views: 494

Answers (2)

Tomáš Záluský
Tomáš Záluský

Reputation: 12030

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

with t (timestamp,data) as (values
  (timestamp '2020-02-02 19:01:21.571429+00', '[
  {
    "tracker_id": "5",
    "position": 1
  },
  {
    "tracker_id": "11",
    "position": 2
  },
  {
    "tracker_id": "4",
    "position": 1
  }
]'::jsonb),
  (timestamp '2020-02-02 19:01:23.571429+00', '[
  {
    "tracker_id": "7",
    "position": 3
  },
  {
    "tracker_id": "4",
    "position": 2
  }
]
'::jsonb),
  (timestamp '2020-02-02 19:02:23.571429+00', '[
  {
    "tracker_id": "5",
    "position": 2
  },
  {
    "tracker_id": "4",
    "position": 1
  }
]
'::jsonb)
), unnested as (
  select t.timestamp, r.tracker_id, r.position
  from t
  cross join lateral jsonb_to_recordset(t.data) AS r(tracker_id text, position int)
)
select count(*)
from unnested u1
join unnested u2
  on u1.tracker_id = u2.tracker_id
 and u1.position = 1 
 and u2.position = 2
 and u1.timestamp < u2.timestamp;

Upvotes: 1

Marc Balmer
Marc Balmer

Reputation: 1830

There are various functions that will help compose several database rows into a single JSON structure: row_to_json(), array_to_json(), and, array_agg().

You will then use the usual SELECT with an ORDER BY clause to get the timestamps/JSON data you want and the use above functions to create a single JSON struture.

Upvotes: 1

Related Questions