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?

It's unclear to me what you want to achieve. Sort the elements of the array? Sort the resulting rows based on the orderNo of the matching productgroup? What if a product has more than one group? Do you want the matching group(s) to be returned as rows? — user330315
– user330315, Commented Mar 1, 2022 at 7:35

user330315 · Accepted Answer · 2022-03-01 07:44:06Z

4

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

answered Mar 1, 2022 at 7:44

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Abraham Arreola Over a year ago

I know this has been inactive for a while, but today I tried this query and it thrown the following error - ERROR: function jsonb_path_query_first(jsonb, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. According to what the error says I guess that I should manually cast the json_path string but how can I achieve this?

bristweb Over a year ago

jsonb_path_query_first is a good tip. postgresql.org/docs/current/…

Laurenz Albe · Accepted Answer · 2022-03-01 07:13:00Z

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

answered Mar 1, 2022 at 7:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

2 Comments

Abraham Arreola Over a year ago

Is there a way to achieve this without the join? I'm working on a project where we have a custom query builder but it can't do joins, I know it's something that needs to be improved but for now I'm just lookin' for a workaround.

Laurenz Albe Over a year ago

I don't think you can avoid that. It is better not to use JSON to store regular tabular data.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related