Postgres查询嵌套的JSONB

Question

我有一个JSONB列，其中包含list of objects.>

下面是表模式：

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

样本数据

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
  }
]

我需要找到tracker_id从position: 1到position: 2的过渡数。

在这里，输出将是2，因为tracker_id 4和5将其position从1更改为2。

Note

转换应该按升序进行，这取决于timestamp

position更改不需要在连续的记录中。

我用的是时间分机

到目前为止，我已经尝试查询单个记录列表中的对象，但我不知道如何合并每个记录的列表对象并查询它们。

对此的查询是什么？我应该把存储过程写下来吗？

Answer 1

我不使用timescaledb扩展，因此我将选择基于取消嵌套的json的纯SQL解决方案：

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;

Answer 2

有各种函数将帮助将几个数据库行组合成一个JSON结构: row_to_json()、array_to_json()和，array_agg()。

然后，您将使用通常的SELECT和ORDER子句来获取您想要的时间戳/ JSON数据，并使用上面的函数来创建单个JSON结构。