CODEWITHSUNDEEP
postgresql
koque
koque

Reputation: 2256

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

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?

Upvotes: 1

Views: 5444

Answers (2)

Ramin Faracov
Ramin Faracov

Reputation: 3303

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}]

Upvotes: 1

Ramin Faracov
Ramin Faracov

Reputation: 3303

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

Upvotes: 0

Related Questions