Custom ordering by JSON column's array of objects in PostgreSQL

Question

I have a json_metrics' column of the json data type in a PostgreSQL 10.9 database's table. The data in the column is an array of objects, with each having a sessions, entrances, month and year attribute. There is at most one object per month/year combination. So it looks like this, for example:

[
  {"sessions":52,"entrances":55,"month":3,"year":2020},
  {"sessions":59,"entrances":59,"month":4,"year":2020},
  {"sessions":76,"entrances":76,"month":5,"year":2020}
]

I would like to be able to sort rows on either "sessions" or "entrances" for a given "month" and "year" combination stored in this json_metrics column. eg. Sort rows by descending 3/2020 sessions. If there was a column for sessions-2020-03 I would, of course, just "order by sessions-2020-03 desc" to do that.

Is what I am trying to do possible with a json column? I am open to changing the structure of the json, or changing the data type to jsonb if that is of any value.

It is possible. If you are using PG12, then you can use SQL jsonpath to extract the correct object for you and then dereference the sessions key directly. If you are on an earlier PG, then you will need to use json_array_elements() in a lateral join to turn this array into rows and use the where clause to filter it and then order by. I would like to help with an example, but I need to run out for the day. — Mike Organek
– Mike Organek, Commented Aug 7, 2020 at 16:07
Thank you for this suggestion @MikeOrganek . I should have included that I am using version 10.9, so it sounds like this might get messy. :) I will have to experiment with going down that path and see how it goes, maybe comparing if there is some better way to organize the json, or if getting all rows and then sorting in code will be preferable. — mmccaff
– mmccaff, Commented Aug 7, 2020 at 16:19
It is not that bad at all. jsonpath would have been easier, but the unnesting is only an extra step. — Mike Organek
– Mike Organek, Commented Aug 7, 2020 at 17:27

Mike Organek · Accepted Answer · 2020-08-08 01:21:37Z

1

With PG10, I would approach the problem something like this:

select mt.*
  from my_table mt
 cross join lateral json_array_elements(my_json_stuff) as j(obj)
 where ((obj->>'year')::int, (obj->>'month')::int) = (2020, 3)
 order by (obj->>'sessions')::int desc;

In response to your comment, if you want to keep rows that do not have a 2020-03 entry, then you can do a left join lateral, which behaves like a left join would. The lateral buys you the implicit join to the row containing the json type that was blown out.

select mt.*
  from my_table mt
  left join lateral json_array_elements(my_json_stuff) as j(obj)
    on ((obj->>'year')::int, (obj->>'month')::int) = (2020, 3)
 order by (obj->>'sessions')::int desc nulls last;

One other note: You do want to use jsonb instead of json going forward.

edited Aug 8, 2020 at 1:21

answered Aug 7, 2020 at 17:40

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mmccaff Over a year ago

Incredible! So, I had given up and done the work in code which performs not nearly as well as this. However, the above does seem to exclude results where there is no matching (2020, 3). Are you able to suggest a modification that would not filter those out, but leave them below these sorted rows? I don't want to add an additional 'where' clause to the overall data that is returned.

Mike Organek Over a year ago

@mmccaff Sorry so late with this--I was out all day. I added an example using left join lateral that works on a sample I cooked up. Please let me know in comments if it gives you problem.

mmccaff Over a year ago

Perfect, I wish that I could give multiple upvotes! Thank you for helping with this, and it's really beautiful how nicely PostgreSQL handles this case. Btw, is performance going to be the main advantage of using jsonb over json? Any disadvantage?

Mike Organek Over a year ago

@mmccaff Yes. Performance is the main advantage of jsonb over json. Regular json may have a size advantage over jsonb. That said, building this out relationally with a calendar table and a metrics table will bury both json types in terms of performance and size.

Collectives™ on Stack Overflow

Custom ordering by JSON column's array of objects in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related