Getting specific key values from JSONB into columns

Question

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

Answer 1

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:

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 here

Assuming that product.product_id the PRIMARY KEY and there is a FOREIGN KEY constraint from order_items.product_id to products.product_id - then the join to products 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:

• What is the difference between LATERAL and a subquery in PostgreSQL?

Aside:
Look at the test setup in the fiddle to see how to best present your table definitions (including constraints!) and values.