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?

If the schema of the "objects" is as static as presented here, don't use arrays, normalize the database and use proper relational means like (linking) tables and columns. That'll save you some headaches later. If it really is dynamic, you may look for JSON data types (jsonb, json). — sticky bit
– sticky bit, Commented Jan 31, 2022 at 19:31
My advice: don't do it. Rather, define a cart_item table with a foreign key to item. Each array member will become a row. — Laurenz Albe
– Laurenz Albe, Commented Jan 31, 2022 at 19:31
If you want to use ad hoc nested structure then use NoSQL. If you are using a SQL database then use SQL structures e.g. table relationships. Combing the two is just making your life harder. — Adrian Klaver
– Adrian Klaver, Commented Jan 31, 2022 at 19:43

Ramin Faracov · Accepted Answer · 2022-02-08 00:01:51Z

I wrote some examples for you

Example 1. (Insert into JSONB type using converting String to JSONB)

INSERT INTO cart 
(
    product_id, 
    quantity, 
    "size", 
    user_id, 
    items
) 
values 
(
    1, 
    1, 
    'Small', 
    1, 
    '[
        {"size": "Small", "quantity": 1, "product_id": 1}, 
        {"size": "Small", "quantity": 1, "product_id": 1}, 
        {"size": "Small", "quantity": 1, "product_id": 1}
     ]'::jsonb
);

Example 2. (Convert fields of selected table to JSONB using row_to_json)

select row_to_json(t1) as your_json from (
    select product_id, "size", quantity from cart
) t1 

Result: 

your_json
--------------------------------------------
{"product_id":1,"size":"Small","quantity":1}
{"product_id":1,"size":"Small","quantity":1}
{"product_id":1,"size":"Small","quantity":1}

Example 3. (Concat all rows of type JSONB)

select jsonb_agg(row_to_json(t1)) as your_json from (
    select product_id, "size", quantity from cart
) t1 

Result: 

your_json
---------------------------------------------------------------------------------------------------------------------------------------------------------
[{"size": "Small", "quantity": 1, "product_id": 1}, {"size": "Small", "quantity": 1, "product_id": 1}, {"size": "Small", "quantity": 1, "product_id": 1}]

Example 4. (Similar of row_to_json)

select  
  json_build_object('product_id', product_id, 'size', size, 'quantity', quantity) as your_json
from cart;

Result: 

your_json
--------------------------------------------
{"product_id":1,"size":"Small","quantity":1}
{"product_id":1,"size":"Small","quantity":1}
{"product_id":1,"size":"Small","quantity":1}

Example 5. (Concatenate many JSONB objects using ||)

select jsonb_build_array('{"product_id":1,"size":"Small","quantity":1}'::jsonb) || jsonb_build_array('{"product_id":1,"size":"Small","quantity":1}'::jsonb) as your_json 
-- or 
select jsonb_build_array('{"product_id":1,"size":"Small","quantity":1}'::jsonb, '{"product_id":1,"size":"Small","quantity":1}'::jsonb) as your_json 

Result:

your_json
------------------------------------------------------------------------------------------------------
[{"product_id": 1, "size": "Small", "quantity": 1}, {"product_id": 1, "size": "Small", "quantity": 1}]

Example 6. (Similar example to your update query (using concatenating JSONB types)

update cart 
set 
    items = jsonb_build_array(items) || jsonb_build_array('{"product_id":1,"size":"Small","quantity":1}'::jsonb)
where id = 1 

Result: 

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

Ramin Faracov · Accepted Answer · 2022-02-06 18:54:32Z

0

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

answered Feb 6, 2022 at 18:54

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

1 Comment

koque Over a year ago

Could you show an insert and update using jsonb?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related