DB lab7

CREATE TABLE table_name (json_value json);
INSERT INTO table_name VALUES ('{"name":"me"}');
delete from table_name;
UPDATE table_name SET json_value = '{"name":"you"}';
select * from table_name;


CREATE TABLE families_j (id serial PRIMARY KEY, profile json);

INSERT INTO families_j (profile) VALUES (
    '{"name":"Gomez", "members":[
        {"member":{"relation":"padre", "name":"Alex"}},
        {"member":{"relation":"madre", "name":"Sonia"}},
        {"member":{"relation":"hijo", "name":"Brandon"}},
        {"member":{"relation":"hija", "name":"Azaleah"}}
    ]}');

SELECT json_array_length(profile->'members') AS num, json_array_elements(profile->'members')#>>'{member, name}' AS name
FROM families_j
WHERE profile->>'name' = 'Gomez';

-- SELECT row_to_json(f) FROM families_j AS f;

SELECT array_to_json(array_agg(f)) AS data
FROM (
    SELECT json_array_length(profile->'members') AS num, (json_array_elements(profile->'members'))#>>'{member, name}' AS name
    FROM families_j
    WHERE profile->>'name' = 'Gomez'
) AS f;

CREATE TABLE families_b (id serial PRIMARY KEY, profile jsonb);

INSERT INTO families_b (profile) VALUES (
    '{"name":"Gomez", "members":[
        {"member":{"relation":"padre", "name":"Alex"}},
        {"member":{"relation":"madre", "name":"Sonia"}},
        {"member":{"relation":"hijo", "name":"Brandon"}},
        {"member":{"relation":"hija", "name":"Azaleah"}}
    ]}');

select * from families_b;

-- Task3
-- Q1
SELECT data->>'OrderKey' AS OrderKey, data->>'TotalPrice' AS TotalPrice FROM ORDERS WHERE data->>'OrderPriority' = '2-HIGH' AND data->>'OrderDate' = '1994-09-29';

-- Q2
select data->>'OrderPriority' as orderPriority, count(*) as totalNumber
from orders
where data->>'OrderDate' > '1998-01-01'
group by data->>'OrderPriority'
order by totalNumber desc;

-- Q3
select table1.shipmode, sum(table1.quantity)
from 
    (select (lineitem.data->>'Quantity')::double precision as quantity, lineitem.data->>'OrderKey' as orderkey, lineitem.data->>'ShipMode' as shipmode
        from lineitem
        where lineitem.data->>'ShipMode' != 'MAIL') as table1
    join 
    (select orders.data->>'OrderKey' as orderkey
        from orders
        where orders.data->>'OrderDate' between '1998-11-01' and '1998-11-30') as table2
    on table1.orderkey = table2.orderkey
group by table1.shipmode;

-- Q4
Select table1.key, table1.name, table1.brand, sum(table2.quantity::double precision) as totalquantity
From 
    (select part.data->>'PartKey' as key, part.data->>'Name' as name, part.data->>'Brand' as brand
        from part) as table1
    Inner join 
    (select lineitem.data->>'Quantity' as quantity, lineitem.data->>'PartKey' as key
        from lineitem
        where lineitem.data->>'ShipDate' between '1993-01-01' and '1995-12-12') as table2
    On table1.key = table2.key
Group by table1.key, table1.name, table1.brand
Order by totalquantity desc
Limit 3;

-- Q5
Select customerTable.key, customerTable.name, customerTable.address, customerTable.phone, getTable.count
From (
    Select O.data->>'CustKey' as getKey, count((O.data->>'CustKey')::int) as count
    From orders as O
    Where ((O.data->>'OrderDate' between '1996-01-01' and '1996-12-31') and exists (
        Select *
        From lineitem
        Where lineitem.data->>'OrderKey' = O.data->>'OrderKey' and lineitem.data->>'ShipMode' = 'AIR'
    ))
    Group by O.data->>'CustKey' 
    Having count((O.data->>'CustKey')::int) >= 2
    ) as getTable
Inner join 
    (select customer.data->>'CustKey' as key, customer.data->>'Name' as name, customer.data->>'Address' as address, customer.data->>'Phone' as phone
    from customer) as customerTable
On customerTable.key = getTable.getKey;

-- select  (profile->'members' from families_j