How do I create a Postgres table with a column that holds an array of objects?

Question

The documentation shows that arrays can be created as either text or integer. I did not find an example for creating an object array. An example is the items column:

CREATE TABLE cart (
_id serial PRIMARY KEY,
user_id Integer UNIQUE NOT NULL,
items varchar [],
FOREIGN KEY (user_id)
REFERENCES users (_id)

Object stored in the items array have quotes around them making them strings instead of objects. A code segment creating objects in items is shown below:

update cart  
set items =
array_append(items, 
'{product_id: ${cartItem.productId}, size: ${cartItem.size}, quantity: ${cartItem.quantity}}')
where _id = ${cart._id}
and user_id = ${userId}
RETURNING *

I am compelled to put quotes around the object value. As a result it is stored as a string in the column with quotes around it.

cart {
  _id: 1,
  user_id: 1,
  items: [
    '{product_id: 1, size: Small, quantity: 1}', 
    '{product_id: 1, size: Small, quantity: 1}', 
    '{product_id: 1, size: Small, quantity: 1}'  
  ]
}

Because the items in the items column are stored as string instead of objects, I cannot correctly iterate over them in my program. For example, items.product_id does not exist.

How do I fix this?

Answer 1

Recommended using JSON or JSONB types. In JSON types you can perform any operations on keys or values, such as filtering, selecting, joining. If you need you can even show JSON keys and values as table record view. Examples:

items: '[
            {"product_id": 1, "size": "Small", "quantity": 1}, 
            {"product_id": 1, "size": "Small", "quantity": 1}, 
            {"product_id": 1, "size": "Small", "quantity": 1}
        ]'


select 
    json_array_elements(items)->'product_id' as product_id, 
    json_array_elements(items)->'size' as "size", 
    json_array_elements(items)->'quantity' as quantity
from cart
where user_id = 1;

Result:

product_id	size	quantity
1	"Small"	1
1	"Small"	1
1	"Small"	1