Postgres Ordering by a Json Array

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.

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?

StackExchange Database Administrators Q#188523, answer score: 5