选择列，并在jsonb列中仅返回满足条件的最后一个元素 [英] Select columns and in a jsonb column only return the last element where meets condition

问题描述

我有表个文档，我想选择列foo和bar。还有条评论列，它是 jsonb 。

I have table documents, I want to select columns foo and bar. And also the column comments which is jsonb.

但是在注释中，我只需要满足条件 isUser的最后一个元素： false 。

But in comments I only need the last element that meets condition "isUser":false.

"select foo, bar, comments from documents 
 where comments @> '[{"isUser":false}]' 
 limit 1 " /*just limit by 1, the latest comment where isUser = false*/

这是json在评论列中的样子：

This is how the json looks liks inside comments column:

[{
    "text": "1 sample lorem ipsum",
    "authorId": "0dcd5a36-2778-4fc4-bbc1-112ed61f1362",
    "timestamp": "2018-11-11T08:46:39.608Z",
    "isUser": false
},{
    "text": "2 sample lorem",
    "authorId": "0dcd5a36-2778-4fc4-bbc1-112ed61f1362",
    "timestamp": "2018-11-11T08:46:41.237Z",
    "isUser": true
},{
...]

对于注释我只需要 isUser：false

推荐答案

您可以使用 jsonb_array_elements .. WITH ORDINALITY 获取订单

select foo, bar, j.comments
from 
  documents cross 
  join lateral jsonb_array_elements(comments) WITH ORDINALITY j(comments, rn)
WHERE 
  (j.comments ->> 'isUser'):: boolean is false
  ORDER BY j.rn DESC LIMIT 1;

编辑

我希望它限于注释中的jsonarray内部的1个json对象

I want it to limit to 1 json object inside the jsonarray in comments

select DISTINCT ON ( foo, bar) foo,bar,comments
FROM 
( select d.foo,d.bar,j.comments,j.rn
from 
  documents d cross 
    join lateral jsonb_array_elements(comments) WITH ORDINALITY j(comments, rn)
WHERE 
  (j.comments ->> 'isUser'):: boolean is false
  ) s
  ORDER BY foo,bar,rn desc  ;

演示

这篇关于选择列，并在jsonb列中仅返回满足条件的最后一个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！