Posgres: Order by ::timestamp asc and desc of jsonb column giving same results, how to order by last timestamp
I have this query which retrieves 1 row based on UUID doc_id of table documents, which also has a field column type jsonb:
select
DISTINCT ON (doc_id)
*
FROM (
select d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by (c.comments ->>'timestamp')::timestamp desc
) as s;
When I try:
order by (c.comments ->>'timestamp')::timestamp desc
I get exact same result. I even tried with timestamptz:
order by (c.comments ->>'timestamp')::timestamptz asc
Sample content of jsonb comments column:
[...
"text": "30",
"timestamp": "2018-11-11T09:13:23.242Z", // older
"isUser": false
,
"text": "31",
"timestamp": "2018-11-11T12:53:48.620Z", // LATEST
"isUser": false
]
as you can see object with text 30 is older, yet it always gets returned in the queries above.
postgresql postgresql-9.1 postgresql-9.4
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
add a comment |
I have this query which retrieves 1 row based on UUID doc_id of table documents, which also has a field column type jsonb:
select
DISTINCT ON (doc_id)
*
FROM (
select d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by (c.comments ->>'timestamp')::timestamp desc
) as s;
When I try:
order by (c.comments ->>'timestamp')::timestamp desc
I get exact same result. I even tried with timestamptz:
order by (c.comments ->>'timestamp')::timestamptz asc
Sample content of jsonb comments column:
[...
"text": "30",
"timestamp": "2018-11-11T09:13:23.242Z", // older
"isUser": false
,
"text": "31",
"timestamp": "2018-11-11T12:53:48.620Z", // LATEST
"isUser": false
]
as you can see object with text 30 is older, yet it always gets returned in the queries above.
postgresql postgresql-9.1 postgresql-9.4
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
In your previous question, you hadn't initially mentioned about using the timestamp, so I addedWITH ORDINALITY. You may omit it. I had usedORDER BYoutside the subquery in my answer, which it seems ,you've changed while copying the answer. See dbfiddle.uk/…
– Kaushik Nayak
Nov 11 '18 at 15:00
add a comment |
I have this query which retrieves 1 row based on UUID doc_id of table documents, which also has a field column type jsonb:
select
DISTINCT ON (doc_id)
*
FROM (
select d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by (c.comments ->>'timestamp')::timestamp desc
) as s;
When I try:
order by (c.comments ->>'timestamp')::timestamp desc
I get exact same result. I even tried with timestamptz:
order by (c.comments ->>'timestamp')::timestamptz asc
Sample content of jsonb comments column:
[...
"text": "30",
"timestamp": "2018-11-11T09:13:23.242Z", // older
"isUser": false
,
"text": "31",
"timestamp": "2018-11-11T12:53:48.620Z", // LATEST
"isUser": false
]
as you can see object with text 30 is older, yet it always gets returned in the queries above.
postgresql postgresql-9.1 postgresql-9.4
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
I have this query which retrieves 1 row based on UUID doc_id of table documents, which also has a field column type jsonb:
select
DISTINCT ON (doc_id)
*
FROM (
select d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by (c.comments ->>'timestamp')::timestamp desc
) as s;
When I try:
order by (c.comments ->>'timestamp')::timestamp desc
I get exact same result. I even tried with timestamptz:
order by (c.comments ->>'timestamp')::timestamptz asc
Sample content of jsonb comments column:
[...
"text": "30",
"timestamp": "2018-11-11T09:13:23.242Z", // older
"isUser": false
,
"text": "31",
"timestamp": "2018-11-11T12:53:48.620Z", // LATEST
"isUser": false
]
as you can see object with text 30 is older, yet it always gets returned in the queries above.
postgresql postgresql-9.1 postgresql-9.4
postgresql postgresql-9.1 postgresql-9.4
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
asked Nov 11 '18 at 13:31
commonSenseCodecommonSenseCode
9,7001676123
9,7001676123
In your previous question, you hadn't initially mentioned about using the timestamp, so I addedWITH ORDINALITY. You may omit it. I had usedORDER BYoutside the subquery in my answer, which it seems ,you've changed while copying the answer. See dbfiddle.uk/…
– Kaushik Nayak
Nov 11 '18 at 15:00
add a comment |
In your previous question, you hadn't initially mentioned about using the timestamp, so I addedWITH ORDINALITY. You may omit it. I had usedORDER BYoutside the subquery in my answer, which it seems ,you've changed while copying the answer. See dbfiddle.uk/…
– Kaushik Nayak
Nov 11 '18 at 15:00
In your previous question, you hadn't initially mentioned about using the timestamp, so I added
WITH ORDINALITY. You may omit it. I had used ORDER BY outside the subquery in my answer, which it seems ,you've changed while copying the answer. See dbfiddle.uk/…– Kaushik Nayak
Nov 11 '18 at 15:00
In your previous question, you hadn't initially mentioned about using the timestamp, so I added
WITH ORDINALITY. You may omit it. I had used ORDER BY outside the subquery in my answer, which it seems ,you've changed while copying the answer. See dbfiddle.uk/…– Kaushik Nayak
Nov 11 '18 at 15:00
add a comment |
1 Answer
1
active
oldest
votes
The order is irrelevant to the final outcome because it only applies to the SELECT statement within which it's used, i.e. your subquery. You then perform another query on those results, using DISTINCT ON which will do whatever calculations and return you the results in some order but probably not the one you want.
To allow you to order in the outer query, the fields you want to use in the order must be accessible at that level. That means the subquery will have to also return the timestamp field, then the outer query can order on that but not select it (to keep the returned columns the same).
select
DISTINCT ON (doc_id)
doc_id, comments
FROM (
select d.doc_id, c.comments, (c.comments ->>'timestamp')::timestamp AS comment_timestamp
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
) as s
ORDER BY doc_id, comment_timestamp DESC
I may be missing something but it seems to be you don't need the subquery anyway, wouldn't this work?
select DISTINCT ON (d.doc_id) d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by d.doc_id, (c.comments ->>'timestamp')::timestamp desc
answered Nov 11 '18 at 14:42
404404
3,0851726
1
Damn IMPRESSIVE! very elegant the final solution, thanks so much It works perfectly.
– commonSenseCode
Nov 11 '18 at 14:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249231%2fposgres-order-by-timestamp-asc-and-desc-of-jsonb-column-giving-same-results%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The order is irrelevant to the final outcome because it only applies to the SELECT statement within which it's used, i.e. your subquery. You then perform another query on those results, using DISTINCT ON which will do whatever calculations and return you the results in some order but probably not the one you want.
To allow you to order in the outer query, the fields you want to use in the order must be accessible at that level. That means the subquery will have to also return the timestamp field, then the outer query can order on that but not select it (to keep the returned columns the same).
select
DISTINCT ON (doc_id)
doc_id, comments
FROM (
select d.doc_id, c.comments, (c.comments ->>'timestamp')::timestamp AS comment_timestamp
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
) as s
ORDER BY doc_id, comment_timestamp DESC
I may be missing something but it seems to be you don't need the subquery anyway, wouldn't this work?
select DISTINCT ON (d.doc_id) d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by d.doc_id, (c.comments ->>'timestamp')::timestamp desc
answered Nov 11 '18 at 14:42
404404
3,0851726
1
Damn IMPRESSIVE! very elegant the final solution, thanks so much It works perfectly.
– commonSenseCode
Nov 11 '18 at 14:44
add a comment |
The order is irrelevant to the final outcome because it only applies to the SELECT statement within which it's used, i.e. your subquery. You then perform another query on those results, using DISTINCT ON which will do whatever calculations and return you the results in some order but probably not the one you want.
To allow you to order in the outer query, the fields you want to use in the order must be accessible at that level. That means the subquery will have to also return the timestamp field, then the outer query can order on that but not select it (to keep the returned columns the same).
select
DISTINCT ON (doc_id)
doc_id, comments
FROM (
select d.doc_id, c.comments, (c.comments ->>'timestamp')::timestamp AS comment_timestamp
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
) as s
ORDER BY doc_id, comment_timestamp DESC
I may be missing something but it seems to be you don't need the subquery anyway, wouldn't this work?
select DISTINCT ON (d.doc_id) d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by d.doc_id, (c.comments ->>'timestamp')::timestamp desc
answered Nov 11 '18 at 14:42
404404
3,0851726
1
Damn IMPRESSIVE! very elegant the final solution, thanks so much It works perfectly.
– commonSenseCode
Nov 11 '18 at 14:44
add a comment |
The order is irrelevant to the final outcome because it only applies to the SELECT statement within which it's used, i.e. your subquery. You then perform another query on those results, using DISTINCT ON which will do whatever calculations and return you the results in some order but probably not the one you want.
To allow you to order in the outer query, the fields you want to use in the order must be accessible at that level. That means the subquery will have to also return the timestamp field, then the outer query can order on that but not select it (to keep the returned columns the same).
select
DISTINCT ON (doc_id)
doc_id, comments
FROM (
select d.doc_id, c.comments, (c.comments ->>'timestamp')::timestamp AS comment_timestamp
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
) as s
ORDER BY doc_id, comment_timestamp DESC
I may be missing something but it seems to be you don't need the subquery anyway, wouldn't this work?
select DISTINCT ON (d.doc_id) d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by d.doc_id, (c.comments ->>'timestamp')::timestamp desc
answered Nov 11 '18 at 14:42
404404
3,0851726
The order is irrelevant to the final outcome because it only applies to the SELECT statement within which it's used, i.e. your subquery. You then perform another query on those results, using DISTINCT ON which will do whatever calculations and return you the results in some order but probably not the one you want.
To allow you to order in the outer query, the fields you want to use in the order must be accessible at that level. That means the subquery will have to also return the timestamp field, then the outer query can order on that but not select it (to keep the returned columns the same).
select
DISTINCT ON (doc_id)
doc_id, comments
FROM (
select d.doc_id, c.comments, (c.comments ->>'timestamp')::timestamp AS comment_timestamp
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
) as s
ORDER BY doc_id, comment_timestamp DESC
I may be missing something but it seems to be you don't need the subquery anyway, wouldn't this work?
select DISTINCT ON (d.doc_id) d.doc_id, c.comments
from documents as d
cross join lateral jsonb_array_elements(comments)
WITH ORDINALITY c(comments)
WHERE (c.comments ->> 'isUser'):: boolean is false
order by d.doc_id, (c.comments ->>'timestamp')::timestamp desc
answered Nov 11 '18 at 14:42
404404
3,0851726
answered Nov 11 '18 at 14:42
404404
3,0851726
answered Nov 11 '18 at 14:42
404404
3,0851726
answered Nov 11 '18 at 14:42
404404
3,0851726
3,0851726
1
Damn IMPRESSIVE! very elegant the final solution, thanks so much It works perfectly.
– commonSenseCode
Nov 11 '18 at 14:44
add a comment |
1
Damn IMPRESSIVE! very elegant the final solution, thanks so much It works perfectly.
– commonSenseCode
Nov 11 '18 at 14:44
1
1
Damn IMPRESSIVE! very elegant the final solution, thanks so much It works perfectly.
– commonSenseCode
Nov 11 '18 at 14:44
Damn IMPRESSIVE! very elegant the final solution, thanks so much It works perfectly.
– commonSenseCode
Nov 11 '18 at 14:44
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249231%2fposgres-order-by-timestamp-asc-and-desc-of-jsonb-column-giving-same-results%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
In your previous question, you hadn't initially mentioned about using the timestamp, so I added
WITH ORDINALITY. You may omit it. I had usedORDER BYoutside the subquery in my answer, which it seems ,you've changed while copying the answer. See dbfiddle.uk/…– Kaushik Nayak
Nov 11 '18 at 15:00