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-&gt;'productCategories'-&gt;0-&gt;&gt;'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-&gt;&gt;'priority' AS prio
   FROM   jsonb_array_elements(t.object_data-&gt;'productCategories') a(elem)
   WHERE  elem-&gt;&gt;'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?