I have a table in PostgreSQL database.
x
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.
Advertisement
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