Skip to content

Instantly share code, notes, and snippets.

@kovid-rathee

kovid-rathee/transformation_example_1.sql

Last active February 25, 2019 09:16
Show Gist options
  • Save kovid-rathee/0f4b93a81a6d4a6a23d51a54169c3c66 to your computer and use it in GitHub Desktop.
Save kovid-rathee/0f4b93a81a6d4a6a23d51a54169c3c66 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. kovid-rathee revised this gist Feb 25, 2019. 1 changed file with 9 additions and 9 deletions.
    18 changes: 9 additions & 9 deletions transformation_example_1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -46,9 +46,9 @@ values (uuid_generate_v4(),
    select id,
    _serial_id,
    array_to_json(order_items) order_items,
    order_items[1] item_name_1,
    order_items[2] item_name_2,
    order_items[3] item_name_3,
    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,
    @@ -61,13 +61,13 @@ select id,
    (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_lat,
    ST_Y(order_location) order_location_lng,
    ST_X(order_location) order_location_latitude,
    ST_Y(order_location) order_location_longitude,
    order_location,
    transformation_utils.ts_to_isodate(created_at) created_date,
    transformation_utils.ts_to_seconds_of_day(created_at) created_time,
    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_date,
    transformation_utils.ts_to_seconds_of_day(updated_at) updated_time,
    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;
  2. kovid-rathee revised this gist Feb 25, 2019. 1 changed file with 14 additions and 5 deletions.
    19 changes: 14 additions & 5 deletions transformation_example_1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -6,7 +6,6 @@ create type order_status as enum (
    'delivered'
    );


    drop table if exists orders;
    create table orders (
    id uuid primary key,
    @@ -15,6 +14,7 @@ create table orders (
    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
    @@ -24,7 +24,8 @@ insert into orders (id,
    order_items,
    payment_response,
    order_status,
    order_location)
    order_location,
    description)
    values (uuid_generate_v4(),
    array['Iced Tea','Naughty Lucy Burger','Large Fries'],
    '{
    @@ -38,16 +39,18 @@ values (uuid_generate_v4(),
    "notes": [],
    "created_at": 1455696913}',
    'placed',
    ST_SetSRID(ST_MakePoint(71.31,24.24),4326)
    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] item_name_1,
    order_items[2] item_name_2,
    order_items[3] item_name_3,
    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,
    @@ -60,5 +63,11 @@ select id,
    (payment_response->'created_at') payment_response_created_at,
    ST_X(order_location) order_location_lat,
    ST_Y(order_location) order_location_lng,
    order_location
    order_location,
    transformation_utils.ts_to_isodate(created_at) created_date,
    transformation_utils.ts_to_seconds_of_day(created_at) created_time,
    created_at,
    transformation_utils.ts_to_isodate(updated_at) updated_date,
    transformation_utils.ts_to_seconds_of_day(updated_at) updated_time,
    updated_at
    from orders;
  3. kovid-rathee revised this gist Feb 25, 2019. 1 changed file with 29 additions and 18 deletions.
    47 changes: 29 additions & 18 deletions transformation_example_1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,19 @@
    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),
    is_deleted boolean default false,
    created_at timestamp without time zone default current_timestamp,
    @@ -11,29 +22,32 @@ create table orders (

    insert into orders (id,
    order_items,
    payment_response,
    payment_response,
    order_status,
    order_location)
    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}',
    ST_SetSRID(ST_MakePoint(71.31,24.24),4326)
    );
    "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)
    );


    -- table 1
    select id,
    order_items item_name,
    array_to_json(order_items) order_items,
    order_items[1] item_name_1,
    order_items[2] item_name_2,
    order_items[3] item_name_3,
    order_status,
    payment_response payment_response,
    (payment_response->'id') payment_response_id,
    (payment_response->'entity') payment_response_entity,
    @@ -47,7 +61,4 @@ select id,
    ST_X(order_location) order_location_lat,
    ST_Y(order_location) order_location_lng,
    order_location
    from orders;

    -- table 2
    select id, unnest(order_items) from orders;
    from orders;
  4. kovid-rathee revised this gist Feb 25, 2019. 1 changed file with 11 additions and 0 deletions.
    11 changes: 11 additions & 0 deletions transformation_example_1.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,14 @@
    create table orders (
    id uuid primary key,
    _serial_id serial,
    order_items text[],
    payment_response json,
    order_location geometry(Point),
    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,
  5. kovid-rathee renamed this gist Feb 25, 2019. 1 changed file with 0 additions and 0 deletions.
    0 sample_transformation.sql → transformation_example_1.sql
    View file
    File renamed without changes.
  6. kovid-rathee created this gist Feb 25, 2019.
    42 changes: 42 additions & 0 deletions sample_transformation.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,42 @@
    insert into orders (id,
    order_items,
    payment_response,
    order_location)
    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}',
    ST_SetSRID(ST_MakePoint(71.31,24.24),4326)
    );

    -- table 1
    select id,
    order_items item_name,
    order_items[1] item_name_1,
    order_items[2] item_name_2,
    order_items[3] item_name_3,
    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_lat,
    ST_Y(order_location) order_location_lng,
    order_location
    from orders;

    -- table 2
    select id, unnest(order_items) from orders;