sql - 在嵌套的 JSON 数组上使用过滤器进行选择

Question

Postgres 10:我在下面有一个表和一个查询:

CREATE TABLE individuals (
    uid character varying(10) PRIMARY KEY,
    data jsonb
);

SELECT data->'files'  FROM individuals WHERE uid = 'PDR7073706'

它返回这个结构:

[
{"date":"2017-12-19T22-35-49","type":"indiv","name":"PDR7073706_indiv_2017-12-19T22-35-49.jpeg"},
{"date":"2017-12-19T22-35-49","type":"address","name":"PDR7073706_address_2017-12-19T22-35-49.pdf"}
]

我很难按日期和时间添加两个过滤器。喜欢(非法伪代码!):

WHERE 'type' = "indiv"

或喜欢:

WHERE 'type' = "indiv"AND max('date')

这可能很容易，但我无法破解这个难题，需要你的帮助!

Answer 1

假设数据类型为jsonb，因为缺少信息。
对第一个子句使用包含运算符 @> (WHERE 'type' = "indiv"):

SELECT data->'files'
FROM   individuals
WHERE  uid = 'PDR7073706'
AND    data -> 'files' @> '[{"type":"indiv"}]';

可以支持各种索引。见:

• Query for array elements inside JSON type
• Index for finding an element in a JSON array

第二个子句 (AND max('date')) 更棘手。假设你的意思是:
获取具有 "type":"indiv" 且具有最新 "date" 的 JSON 数组元素的行。

SELECT i.*
FROM   individuals i
JOIN   LATERAL (
   SELECT *
   FROM   jsonb_array_elements(data->'files')
   ORDER  BY to_timestamp(value ->> 'date', 'YYYY-MM-DD"T"HH24-MI-SS') DESC NULLS LAST
   LIMIT  1
   ) sub ON sub.value -> 'type' = '"indiv"'::jsonb
WHERE  uid = 'PDR7073706'
AND    data -> 'files' @> '[{"type":"indiv"}]' -- optional; may help performance

to_timestamp(value ->> 'date', 'YYYY-MM-DD"T"HH24-MI-SS') 是我对你未声明的时间戳格式的有根据的猜测。 Details in the manual here.

最后一个过滤器是多余的和可选的。但如果它是选择性的(只有几行符合条件)并且您有一个匹配的索引，它可能会(很多)提高性能:

AND    data -> 'files' @> '[{"type":"indiv"}]'

相关:

• Optimize GROUP BY query to retrieve latest record per user
• Select first row in each GROUP BY group?
• Update nth element of array using a WHERE clause