JSON array to a single row in SQL table

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.

Answer

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