Postgres - order records based on a property inside an array of json objects

Question

I'm working with a Postgres database and I have a products view like this:

id	name	product_groups
1	product1	[{...}]
2	product2	[{...}]

the product_groups field contains an array of json objects with the product groups data that the product belongs to, where each json object has the following structure:

{
  "productGroupId": 1001,
  "productGroupName": "Microphones"
  "orderNo": 1,
}

I have a query to get all the products that belong to certain group:

SELECT * FROM products p WHERE p.product_groups @> [{"productGroupId": 1001}]

but I want to get all the products ordered by the orderNo property of the group that I'm querying for.

what should I add/modify to my query in order to achieve this?

Answer 1

I am not really sure I understand your question. My assumptions are:

• there will only be one match for the condition on product groups
• you want to sort the result rows from the products table, not the elements of the array.

If those two assumptions are correct, you can use a JSON path expression to extract the value of orderNo and then sort by it.

SELECT p.* 
FROM products p 
WHERE p.product_groups @> [{"productGroupId": 1001}]
ORDER BY jsonb_path_query_first(p.product_groups, '$[*] ? (@.productGroupId == 1001).orderNo')::int

Answer 2

You have to unnest the array:

SELECT p.*
FROM products AS p
   CROSS JOIN LATERAL jsonb_array_elements(p.product_groups) AS arr(elem)
WHERE arr.elem @> '{"productGroupId": 1001}'
ORDER BY CAST(arr.elem ->> 'orderNo' AS bigint);