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 | |
SELECT version()
. Typically, both are the same, but not necessarily. Also, the key sentence is unclear:I would like to specifically names to a column, value to another column
. Did you mean: ... list the nested keys "name" and "value" as separate columns - in multiple rows. – Erwin Brandstetter Apr 7 '18 at 13:02CREATE TABLE
statements, which allows immediate testing. (You might omit columns that are completely irrelevant to the question to reduce noise.) – Erwin Brandstetter Apr 7 '18 at 13:10product.product_id
thePRIMARY KEY
? Is there aFOREIGN KEY
constraint fromorder_items.product_id
toproducts.product_id
? – Erwin Brandstetter Apr 7 '18 at 13:35