Sample transformation #1

transformation_example_1.sql

create type order_status as enum (
    'placed',
    'accepted',
    'cancelled_by_customer',
    'cancelled_by_restaurant',
    'delivered'
);

drop table if exists orders;
create table orders (
	id uuid primary key,
	_serial_id serial,
	order_items text[],
	payment_response json,
	order_status order_status,
	order_location geometry(Point),
	description text,
	is_deleted boolean default false,
    created_at timestamp without time zone default current_timestamp,
    updated_at timestamp without time zone default current_timestamp
);

insert into orders (id, 
                    order_items, 
                    payment_response,
                    order_status,
                    order_location,
                    description) 
values (uuid_generate_v4(), 
        array['Iced Tea','Naughty Lucy Burger','Large Fries'],
        '{
    "id": "order_7IZKKI4Pnt2kEe",
    "entity": "order",
    "amount": 60000,
    "currency": "INR",
    "receipt": "rcptid33",
    "status": "created",
    "attempts": 0,
    "notes": [],
    "created_at": 1455696913}', 
        'placed',
        ST_SetSRID(ST_MakePoint(71.31,24.24),4326),
        'thisisarandom�character'
       );

select id, 
       _serial_id,
       array_to_json(order_items) order_items,
       order_items[1] order_item_1,
       order_items[2] order_item_1,
       order_items[3] order_item_1,
       order_status,
       transformation_utils.remove_invalid_characters(description) description,
       payment_response payment_response,
       (payment_response->'id') payment_response_id,
       (payment_response->'entity') payment_response_entity,
       (payment_response->'amount') payment_response_amount,
       (payment_response->'currency') payment_response_currency,
       (payment_response->'receipt') payment_response_receipt,
       (payment_response->'status') payment_response_status,
       (payment_response->'notes') payment_response_notes,
       (payment_response->'attempts') payment_response_attempts,
       (payment_response->'created_at') payment_response_created_at,
       ST_X(order_location) order_location_latitude,
       ST_Y(order_location) order_location_longitude,
       order_location,
       transformation_utils.ts_to_isodate(created_at) created_at_date,
       transformation_utils.ts_to_seconds_of_day(created_at) created_at_time,
       created_at,
       transformation_utils.ts_to_isodate(updated_at) updated_at_date,
       transformation_utils.ts_to_seconds_of_day(updated_at) updated_at_time,
       updated_at 
from orders;