Блог

У меня есть таблица в базе данных PostgreSQL.

 CREATE TABLE Receipts
(
    shop TEXT,
    receipt JSONB
);
 

С некоторыми данными в нем:

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

receipt столбец содержит списки из 3-элементных списков.

Чего я пытаюсь добиться, так это написать SQL-запрос, который возвращает таблицу в виде

  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
 

Лучшее, что у меня есть на данный момент, — это

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

но я застрял на том, чтобы не использовать внутренние массивы. Я пытался использовать jsonb_to_record , но, похоже, работает с объектами JSON, а не с массивами. Я буду признателен за ваш совет.

Вы были почти на месте, вам нужно только извлечь каждый элемент из массива, который вы извлекли, как вы можете видеть ниже

 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;
 

дает результат:

  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
 

Вы можете сделать это с помощью бокового соединения.

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