1

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 |              |
Improve this question
5
  • You have posted a number of questions recently and you are welcome. But you need to include more useful information. Postgres version, table definition, desired result. Please clarify your questions. – Erwin Brandstetter Apr 6 '18 at 16:07
  • @ErwinBrandstetter Hope that my clarification is enough. Thanks – SNaRe Apr 7 '18 at 9:43
  • Note that psql is the client program, which has its own version. You get the server version with 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:02
  • BTW, the question is much better now. If possible provide table definitions as CREATE 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:10
  • The essential constraints are still missing. Is product.product_id the PRIMARY KEY? Is there a FOREIGN KEY constraint from order_items.product_id to products.product_id ? – Erwin Brandstetter Apr 7 '18 at 13:35
0

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:

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

Improve this answer
1
  • This is almost what I needed. LEFT JOIN LATERAL jsonb_to_recordset(attributes) a(name text, value text) ON true; was what I am looking for. Thanks – SNaRe Apr 7 '18 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.