How to get many values from a single JSON column in PostgreSQL?

Question

In my DB, I currently have a table that goes like this:

order_id	country	data
1	Germany	{"selectedProducts": [{"product": {"size": 2.5, "id": 72}, "quantity": 1},{"product": {"size": 3, "id": 25}, "quantity": 1}]}
2	Germany	{"selectedProducts": [{"product": {"size": 4, "id": 72}, "quantity": 1},{"product": {"size": 3, "id": 36}, "quantity": 1}]}
3	Italy	{"selectedProducts": [{"product": {"size": 2.5, "id": 72}, "quantity": 1}]}

And I've been trying to come up with a SELECT sql query that would allow me to fetch the products' id to be able to study its popularity by country and such, but I haven't been successful at all doing so.

The data column can hold an indeterminate number of products, too. Care to help? I've already tried using the -> and ->> operators in PostgreSQL to no avail, but it might have been me not using them properly

Julius Tuskenis · Accepted Answer · 2021-06-23 06:36:07Z

1

You should use json_array_elements to extract all the json array elements. Example:

SELECT
  id, country, json_array_elements(inventory->'selectedProducts')
FROM 
  products

To try: db<>fidle

After that you are left with products and can use -> or ->> operators to extract a single attribute by its name or use json_each to extract all key-value pairs.

answered Jun 23, 2021 at 6:36

Julius Tuskenis

1,63010 silver badges15 bronze badges

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

1 Comment

CelticJordan Over a year ago

Thank you so much, finally I can keep going with this DB's analysis :D

Collectives™ on Stack Overflow

How to get many values from a single JSON column in PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related