Postgresql
從 JSONB 獲取特定鍵值到列中
在此查詢
attributes中，一個 JSONB 列：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我有這樣的數據：
[{"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ı"}]我想專門為一列命名，為另一列命名。期望的結果是選擇屬性並將它們放在 2 列中“名稱”“值”作為列名，並將其下列出的內容作為行。
我怎樣才能做到這一點？我閱讀了很多網站，但我無法成功。
PostgreSQL 版本：psql (PostgreSQL) 10.3 (Ubuntu 10.3-1.pgdg16.04+1)
products表定義：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桌子：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 | |
不確定您的確切目標。您顯示的查詢表示您希望計算列中相同的值，
attributes並在每個單獨的行中列出鍵“名稱”和“值”。這個查詢會這樣做：
SELECT * FROM ( SELECT COUNT(attributes) AS sayi, attributes FROM order_items GROUP BY attributes ) oi LEFT JOIN LATERAL jsonb_to_recordset(attributes) a(name text, value text) ON true;dbfiddle在這裡
假設and 有一個
product.product_id約束from to - 那麼連接 to只是這個特定查詢的冗餘雜訊。所以我放棄了它。PRIMARY KEY``FOREIGN KEY``order_items.product_id``products.product_id``products主要特點是
jsonb_to_recordset()。關於無條件
LEFT JOIN LATERAL：旁白：
查看 fiddle 中的測試設置，了解如何最好地呈現您的表定義（包括約束！）和值。