代码之家 › 专栏 › 技术社区 › silverfighter

在jsonb列中查询值为最大日期的json属性

jsonb postgresql sql

silverfighter · 技术社区 · 7 年前

我有一个postgres表,其中有一个jsonb列,一旦达到某个状态,就会插入/更新该列。我想查询最新状态及其日期。

给定以下jsonb状态列。

{
 "pending": "2018-01-12T12:34:41.785945+00:00",
 "started":"2018-01-10T15:52:41.785945+00:00",
 "processed":"2018-01-18T12:52:41.785945+00:00"
}

要获取的查询是什么?

"processed":"2018-01-18T12:52:41.785945+00:00" ...

基本上使用 max date ,这有可能吗?如果是这样的话,这个查询会是什么样子?

编辑 :状态JSON可以在不同的时间具有不同的属性。因此,它并不总是3个“待定”、“已启动”、“已处理”。只有待决的或将来的其他。。。。问题是最新状态和日期。

1 回复 | 直到 5 年前

timmur 7 年前

让我们用几个示例数据创建表。

DROP TABLE IF EXISTS test;

CREATE TABLE test(
id integer,  
status json  
);

INSERT INTO test VALUES (1, '{
 "pending": "2018-01-12T12:34:41.785945+00:00",
 "started":"2018-01-10T15:52:41.785945+00:00",
 "processed":"2018-01-18T12:52:41.785945+00:00"
}');
INSERT INTO test VALUES (2, '{
 "pending": "2018-01-12T12:31:41.785945+00:00",
 "started":"2018-01-20T15:55:41.785945+00:00",
 "processed":"2018-01-10T12:20:41.785945+00:00"
}');

我设法得到了 max date 属性,方法是将json中的值提取到一个单独的表中,并应用典型的max函数和case语句来获取相应的感兴趣的属性。这可能会破坏使用json类型的目的(可能改为尝试json\u类型(json))

--- unpack values and put them into different columns
WITH t1 AS(
SELECT id, status->>'pending' AS pending, 
      status->>'started' AS started, 
      status->>'processed' AS processed       
FROM test),
--- find maximum time corresponding to each_id
t2 AS (
SELECT id, GREATEST(t1.pending, t1.processed, t1.started) AS latest
FROM t1
)
--- join to get matching column that gave latest time
SELECT t1.id, CASE WHEN t1.pending = t2.latest THEN 'pending'
                  WHEN t1.started = t2.latest THEN 'started'
                  WHEN t1.processed = t2.latest THEN 'processed' 
                  END AS max_status,
              t2.latest AS max_time
FROM t1
JOIN t2
ON t1.id = t2.id
;

您可以为每个 id . 此结果

 id | max_status |             max_time             
----+------------+----------------------------------
  1 | processed  | 2018-01-18T12:52:41.785945+00:00
  2 | started    | 2018-01-20T15:55:41.785945+00:00
(2 rows)

编辑:: 编辑之后,我做了一些更改,以处理json字段中特定键的任意存在/不存在。让我们使用 json_each(json) 打开包装 key, value 对于每个 身份证件 到不同的列

SELECT id, (json_each(status)).* 
FROM test
WHERE id=1;

 id |    key    |               value                
----+-----------+------------------------------------
  1 | pending   | "2018-01-12T12:34:41.785945+00:00"
  1 | started   | "2018-01-10T15:52:41.785945+00:00"
  1 | processed | "2018-01-18T12:52:41.785945+00:00"
(3 rows)

不完全是我们想要的,但几乎达到了。首先要注意的是列名 键,值 我们可以用它来转换数据。其次,价值观是 json 对象,因此我们必须在转换为文本后注意双重引用。 trim 处理得很好。Rest是按 身份证件 ,获取最长时间,并筛选行以获取相应的 status .

WITH t1 AS(
    SELECT id, key as status, trim(both '"' from value::text) as time_of 
    FROM test, json_each(status)
),
t2 as(
SELECT id, status,
  to_timestamp(time_of, 'YYYY-MM-DD"T"HH24:MI:SS') as time_of,
  MAX(to_timestamp(time_of, 'YYYY-MM-DD"T"HH24:MI:SS')) 
        OVER(PARTITION by id) AS max_time       
FROM t1)
SELECT id, status, max_time 
FROM t2
WHERE time_of = max_time;