Postgres Ordering by a Json Array

Question

I have the following json that I am storing in a jsonb field. For every record representing a product, it contains various categories and in each category a is priority level.

[
 {
 "priority": 0,
 "description": "",
 "categoryName": "Cars",
 "title": ""
 },
 {
 "priority": 1,
 "description": "",
 "categoryName": "Trucks",
 "title": ""
 },
 {
 "priority": 2,
 "description": "",
 "categoryName": "Vans",
 "title": ""
 }
 ]

I would like to order the data by various categories dynamically using the priority field such that, if I am displaying all the products in the category Cars, I will order by the 0 array element, if I am displaying all the products in the category Vans, I order by the 2 array element.

Currently, to order by the first array element I am able to do so:

ORDER BY OBJECT_DATA->'productCategories'->0->>'priority' 

However, I would like to order by first selecting the the array value (categoryName) and then using the array value to select its priority.

As an example: order by Trucks->priority (1)

I am using PostgresSQL 9.6.

Answer 1

Assuming from your code snippet that the displayed JSON is the value of a key "productCategories" in a jsonb column object_data of your table tbl, you can achieve your desired sort order like this:

SELECT t.*
FROM tbl t
LEFT JOIN LATERAL (
 SELECT elem->>'priority' AS prio
 FROM jsonb_array_elements(t.object_data->'productCategories') a(elem)
 WHERE elem->>'categoryName' = 'Trucks' -- your category here
 ) a ON true -- preserve all rows from t
ORDER BY prio; -- NULL values are sorted last

Related answers with detailed explanation:

• How to sort objects in an array inside a json or jsonb value by a property of the objects?
• Customize jsonb key sort order involving arrays
• What is the difference between LATERAL and a subquery in PostgreSQL?
• PostgreSQL sort by datetime asc, null first?