4

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.

2

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.