Как распарсить JSON файл с помощью SQL

Вводные данные:

У вас есть таблица с огромным кол-вом столбцов. У вас есть JSON файл, содержимое которого вам необходимо разложить по соответствующими столбцам таблицы.
Руками делать это долго, так как JSON файлы поступают регулярно.

Решение задачи:

Шаг_1. Создаем таблицу lamoda_orders со столбцами

CREATE TABLE lamoda_orders (
id text NULL,
ordernr text NULL,
status text NULL,
paymentmethod text NULL,
rejectprice text NULL,
deliveryprice text NULL,
paymentdate text NULL,
isconfirmed bool NULL,
"comment" text NULL,
itemquantity int4 NULL,
createdat date NULL,
updatedat timestamp NULL,
delivered text NULL,
shipped timestamp NULL,
deprecatedid int8 NULL,
fullsum numeric NULL,
currency text NULL,
hasdiscrepancyacts bool NULL,
region text NULL,
city text NULL,
cityid text NULL,
isvalid bool NULL,
deliveryintervalid int8 NULL,
deliverydate text NULL,
deliveryintervalfrom text NULL,
deliveryintervalto text NULL,
isdeliverypricecustom bool NULL,
standarddeliveryprice text NULL,
deliverydatemax text NULL,
deliverydatemin text NULL,
shippingmethodname text NULL,
shippingmethodcode text NULL,
serviceleveltypecode text NULL,
serviceleveltypename text NULL,
storagedays text NULL,
cutoff text NULL,
hasintervals bool NULL,
code text NULL,
shopname text NULL,
partnerid int8 NULL,
externaldeliveryinfo text NULL,
rnk int8 NULL
);

Шаг_2. Создаем функцию parse_json_data

CREATE OR REPLACE FUNCTION public.parse_json_data(pdata jsonb)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public', 'pg_temp'
AS $function$
DECLARE
d record;
e record;
o record;
r record;
s record;
a record;
BEGIN
TRUNCATE public.lamoda_orders;

SELECT * INTO d FROM jsonb_to_record(pData) AS x(page int, "limit" int, pages int, total int, _links jsonb, _embedded jsonb);
SELECT * INTO e FROM jsonb_to_record(d._embedded) AS x(orders jsonb);

FOR o IN SELECT * FROM jsonb_to_recordset(e.orders) AS x(id text, "orderNr" text, status text, "paymentMethod" text,
"rejectPrice" numeric, "deliveryPrice" numeric, "paymentDate" timestamptz, "isEditable" bool, "isConfirmed" bool,
comment text, "itemQuantity" int, "createdAt" timestamptz, "updatedAt" timestamptz, dates jsonb, "deprecatedId" int,
"fullSum" numeric, currency text, "hasDiscrepancyActs" bool, _links jsonb, _embedded jsonb)
LOOP
SELECT * INTO r FROM jsonb_to_record(o._embedded) AS x("shippingAddress" jsonb, "deliveryMethod" jsonb, customer jsonb, sessions jsonb, partner jsonb, payments jsonb, "externalDeliveryInfo" jsonb);
SELECT * INTO s FROM jsonb_to_record(r."shippingAddress") AS x(id bigint, zipcode text, region text, city text, street text, "houseNum" text, apartment text, coordinate jsonb, "cityId" text, "streetId" text, "buildingId" text, "isValid" bool, "isEditable" bool, "pickupPointId" text);
SELECT * INTO a FROM jsonb_to_record(r."deliveryMethod") AS x("deliveryIntervalId" bigint, "deliveryDate" timestamptz, "deliveryIntervalFrom" text, "deliveryIntervalTo" text, "deliveryPrice" text);

INSERT INTO public.lamoda_orders
SELECT o.id, o."orderNr", o.status, o."paymentMethod", o."rejectPrice", o."deliveryPrice", o."paymentDate", o."isConfirmed", o.comment,
o."itemQuantity", o."createdAt", o."updatedAt", o.dates->>'delivered', (o.dates->>'shipped')::timestamptz, o."deprecatedId", o."fullSum", o.currency, o."hasDiscrepancyActs",
s.region, s.city, s."cityId", s."isValid";
END LOOP;
END
$function$

Шаг_3. Кладем JSON скрипт в качестве аргумента на выполнение нашей созданной функции.

SELECT public.parse_json_data('{
--Сюда копируете содержимое вашего JSON файла
}'::jsonb);

Готово! Данные из скрипта сопоставились со столбцами таблицы.

✔️Содержимое JSON файла, использовавшегося для загрузки находится здесь

✔️Благодарю за прочтение.

✔️Больше полезного на этом обучающем сайте

✔️ YouTube-канал с бесплатными уроками по SQL.