SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Tree ( id int, N int, P int ); INSERT INTO Tree VALUES (1, 1, 2), (2, 3, 2), (3, 6, 8), (4, 9, 8), (5, 2, 5), (6, 8, 5), (7, 5, null); SELECT * FROM Tree; select n node, case when p is null then 'Root' when n in (select p from tree) then 'Inner' else 'Leaf' end from tree order by n; create table json_index ( id varchar (100), N int, op_date timestamp, num real, str char(30), json_indexed jsonb, json_mas_indexed jsonb, json2_unindexed jsonb, json_mas_unindexed jsonb ); /* insert into json_index select main_part.*, to_json(json_rows.*), to_json(json_rows.*) from (select uuid_generate_v4() ,generate_series(1, 10) order_num , now() - (random() * (interval '90 days')) ,substr(md5(random()::text), 1, 30)) main_part inner join (select now() - (random() * (interval '90 days')) as json_datetime ,generate_series(1, 10) as json_order_number ,random()/random() as json_num ,uuid_generate_v4() json_uuid ,substr(md5(random()::text), 1, 60) json_str ) json_rows -- генерация случайных значений для JSON on main_part.order_num = json_rows.json_order_number ;*/ insert into json_index /* Альтернативный вариант select main_part.*, to_json(json_rows.*) json_obj, (select jsonb_agg(json_mas) from (SELECT generate_series(1, round(100*json_rows.json_num --добавляет случайности в массив *random()/random())::int) as json_mass_order_number --случайное количестов строк в массиве ,random()/random() as json_mas_num ,uuid_generate_v4() json_mas_uuid ,substr(md5(random()::text), 1, 60) json_mas_str) json_mas) json_mas from (select uuid_generate_v4() ,generate_series(1, 5) order_num -- задает количество строк в таблице , now() - (random() * (interval '90 days')) ,substr(md5(random()::text), 1, 30)) main_part inner join (SELECT now() - (random() * (interval '90 days')) as json_datetime ,generate_series(1, 5) as json_order_number -- генерирует строки JSON в таблицу ,random()/random() as json_num ,uuid_generate_v4() json_uuid ,substr(md5(random()::text), 1, 60) json_str) json_rows on main_part.order_num = json_rows.json_order_number;*/ select main_part.* ,(select to_json(json_rows.*) from (SELECT now() - (random() * (interval '90 days')) as json_datetime ,main_part.order_num as json_order_num /*добавляет случайности в подзапрос создания поля с JSON объектом*/ ,random()/random() as json_num ,uuid_generate_v4() as json_uuid ,substr(md5(random()::text), 1, 60) json_str) json_rows) json_obj ,(select jsonb_agg(json_mas) from (SELECT generate_series(1, round(100*main_part.random_num/*добавляет случайности в массив JSON *//random())::int) as json_mass_order_number --случайное количестов строк в массиве ,random()/random() as json_mas_num ,uuid_generate_v4() json_mas_uuid ,substr(md5(random()::text), 1, 60) json_mas_str) json_mas) json_mas from (select uuid_generate_v4() ,generate_series(1, 10) order_num ,now() - (random() * (interval '90 days')) ,random()/random() as random_num ,substr(md5(random()::text), 1, 30)) main_part; select * from json_index