Select columns and in a jsonb column only return the last element where meets condition

Question

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

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*/

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
},{
...]

For comments I only need the last object in which "isUser":false

Evasive

Show us some sample of how the json actually looks like and what output you want to see from it. – Invitation

Answer 1

You may use jsonb_array_elements .. WITH ORDINALITY to get the order

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;

EDIT

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  ;

Demo

Invitation

thanks, kinda works when I add the limit 1 at the end but is not getting the last in json array, is insertion order not warrantied? – Evasive

@commonSenseCode : check now. – Invitation

Some progress, so I tested inserting more values. Right now this is limiting the main row return (foo, bar, comments), I want it to limit to 1 json object inside the jsonarray in comments, not to limit the rows. – Evasive

@commonSenseCode : It's basically the TOP 1 per group problem. You may use DISTINCT ON. check edit. Please remember that when you ask a question, your sample data and expected output should reflect the complete requirement. – Invitation

Hi Kaushik this is way nearer to what I need, is there a wat to order DESC by field timestamp inside comments. Somehow sometimes it doesn't give the last added comment – Evasive

@commonSenseCode : Also note that I have assumed here that foo and bar to be the distinct columns, but if you have another unique id in the table, you should use that in the DISTINCT ON and matching column(s) in order by – Invitation

@commonSenseCode : Please note that this answers your original question. If you think you found it useful you may accept it. If you have further questions ask that as a new one. Thanks – Invitation

so I'm trying the end orderby: ORDER BY (rn.comments->>'timestamp')::timestamp DESC but doesn't work. If you have an idea I appreciate further help – Evasive