In this query attributes
a JSONB column:
select
COUNT(order_items.attributes) as sayi,
order_items.attributes
from order_items
INNER JOIN products ON order_items.product_id = products.product_id
GROUP BY order_items.attributes
I have data like this:
[{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}]
[{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}]
[{"name": "Kampanya Seçimi", "value": "USB Çakmaklık (7,99 TL)"}, {"name": "İp, Dikiş Rengi", "value": "Kırmızı"}]
I would like to specifically names to a column, value to another column. Desired result is to select attribute and put them in 2 columns "name" "value" as column name, and the contents listed under it as rows.
How can I do this? I read a lot of website but I couldn't succeed.
PostgreSQL version: psql (PostgreSQL) 10.3 (Ubuntu 10.3-1.pgdg16.04+1)
products
table definition:
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------------+--------------------------------+-----------+----------+----------------------------------------------+----------+--------------+-------------
product_id | integer | | not null | nextval('products_product_id_seq'::regclass) | plain | |
display_price | numeric(8,2) | | not null | | main | |
marketplace_product_id | text | | not null | | extended | |
title | text | | not null | | extended | |
subtitle | text | | | | extended | |
seller_id | integer | | not null | | plain | |
deleted_at | timestamp(0) without time zone | | | | plain | |
created_at | timestamp(0) without time zone | | | | plain | |
updated_at | timestamp(0) without time zone | | | | plain | |
images | jsonb | | | | extended | |
seller_stock_code | text | | | | extended | |
order_items
table:
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------------------+--------------------------------+-----------+----------+----------------------------------------------------+----------+--------------+-------------
order_item_id | integer | | not null | nextval('order_items_order_item_id_seq'::regclass) | plain | |
order_id | integer | | not null | | plain | |
marketplace_item_id | text | | not null | | extended | |
product_id | integer | | | | plain | |
shipping_fee_type_id | integer | | not null | | plain | |
cargo_code | character varying(255) | | | | extended | |
seller_coupon_amount | numeric(8,2) | | | | main | |
mall_discount_amount | numeric(8,2) | | | | main | |
item_variety_stock_code | character varying(255) | | | | extended | |
item_price_without_discounts | numeric(8,2) | | | | main | |
invoice_amount | numeric(8,2) | | not null | | main | |
quantity | integer | | not null | | plain | |
commision | numeric(8,2) | | | | main | |
shipping_carrier_id | integer | | | | plain | |
tracking_code | text | | | | extended | |
shipment_number | character varying(255) | | | | extended | |
shipment_number_status | text | | | | extended | |
deleted_at | timestamp(0) without time zone | | | | plain | |
created_at | timestamp(0) without time zone | | | | plain | |
updated_at | timestamp(0) without time zone | | | | plain | |
attributes | jsonb | | | | extended | |
Best Answer
Not sure about your precise objective. The query you display says you want to count identical values in the column
attributes
and list the keys "name" and "value" in separate rows for each.This query would do that:
dbfiddle here
Assuming that
product.product_id
thePRIMARY KEY
and there is aFOREIGN KEY
constraint fromorder_items.product_id
toproducts.product_id
- then the join toproducts
is just redundant noise for this particular query. So I dropped it.The key feature is
jsonb_to_recordset()
.About the unconditional
LEFT JOIN LATERAL
:Aside:
Look at the test setup in the fiddle to see how to best present your table definitions (including constraints!) and values.