JSON array to a single row in SQL table

Question

I have a table in PostgreSQL database.

CREATE TABLE Receipts
(
    shop TEXT,
    receipt JSONB
);

With some data in it:

 shop |                    receipt                    
------+-----------------------------------------------
 ABC  | [["butter", 1, 3.45], ["potatoes", 1.5, 2.50]]
 XYZ  | [["butter", 1, 2.99], ["apples", 2.0, 1.80]]

receipt column contains lists of 3-element lists.

What I'm trying to achieve is write an SQL query that returns table in the form

 shop |   product  | quantity |  price           
------+------------+----------+-----------
 ABC  |  'butter'  |    1     |  3.45
 ABC  | 'potatoes' |    1.5   |  2.50
 XYZ  |  'butter'  |    1     |  2.99
 XYZ  |  'apples'  |    2.0   |  1.80

The best I've got so far is

# SELECT shop, jsonb_array_elements(receipt) FROM Receipts;
 shop |  jsonb_array_elements  
------+------------------------
 ABC  | ["butter", 1, 3.45]
 ABC  | ["potatoes", 1.5, 2.5]
 XYZ  | ["butter", 1, 2.99]
 XYZ  | ["apples", 2.0, 1.8]
(4 rows)

but I'm stuck on unnesting the inner arrays. I tried using jsonb_to_record, but is seems to work with JSON objects, not arrays. I will appreciate your advice.

NuLo · Accepted Answer · 2021-09-03 19:07:19Z

2

You were almost there, you only have to fetch each element from the array you extracted as you can see below

with lines as (
  SELECT shop, jsonb_array_elements(receipt) as arr
  FROM receipts
)
select shop, arr->>0 as product, arr->>1 as quantity, arr->>2 as price
from lines;

gives the result:

 shop | product  | quantity | price 
------+----------+----------+-------
 ABC  | butter   | 1        | 3.45
 ABC  | potatoes | 1.5      | 2.50
 XYZ  | butter   | 1        | 2.99
 XYZ  | apples   | 2.0      | 1.80

answered Sep 3, 2021 at 19:07

NuLo

1,5381 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stefanov.sm · Accepted Answer · 2021-09-03 19:09:45Z

2

You can do this using a lateral join.

select 
    r.shop, 
    l.ja->>0 product, 
    (l.ja->>1)::numeric quantity, 
    (l.ja->>2)::numeric price  
  from receipts r
  cross join lateral jsonb_array_elements(receipt) l(ja);

answered Sep 3, 2021 at 19:09

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

JSON array to a single row in SQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related