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

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?

CodePudding user response：

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);

CodePudding user response：

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