本文介绍了选择列,并在jsonb列中仅返回满足条件的最后一个元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有表个文档
,我想选择列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对象
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列中仅返回满足条件的最后一个元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!