Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT ow.*, st.*, ma.*
- FROM owner ow
- INNER JOIN st.store ON ow.OwnerId = st.OwnerId
- INNER JOIN machine ma ON ma.StoreId = st.StoreId;
- {
- "OwnerId": "1d2dd",
- "Name": "name test",
- "Store":[{
- "StoreId": "s3ss5",
- "Name": "Store1",
- "Code": "bla",
- "Machine":[{
- "MachineId": "axpeo",
- "Name": "Machine1",
- "Type": "type1"
- }]
- },
- {
- "StoreId": "ddf22",
- "Name": "Store2",
- "Code": "ble",
- "Machine":[{
- "MachineId": "weds",
- "Name": "Machine2",
- "Type": "type2"
- },
- {
- "MachineId": "axdso",
- "Name": "Machine3",
- "Type": "type3"
- }]
- }]
- }
- SELECT json_build_object('OwnerId', ownerid,
- 'Name', name,
- 'Store', stores)
- FROM owner
- JOIN (
- SELECT ownerid,
- json_agg(
- json_build_object('StoreId', storeid,
- 'Name', name,
- 'Code', code,
- 'Machine', machines)) AS stores
- FROM store
- JOIN (
- SELECT storeid,
- json_agg(
- json_build_object('MachineId', machineid,
- 'Name', name,
- 'Type', type)) AS machines
- FROM machine
- GROUP BY storeid) m USING (storeid)
- GROUP BY ownerid) s USING (ownerid);
- [{
- "OwnerId": "1d2dd",
- "Name": "name test",
- "Store":{
- "StoreId": "s3ss5",
- "Name": "Store1",
- "Code": "bla",
- "Machine":{
- "MachineId": "axpeo",
- "Name": "Machine1",
- "Type": "type1"
- }
- }
- },
- {
- "OwnerId": "1d2dd",
- "Name": "name test",
- "Store":{
- "StoreId": "ddf22",
- "Name": "Store2",
- "Code": "ble",
- "Machine":{
- "MachineId": "weds",
- "Name": "Machine2",
- "Type": "type2"
- }
- }
- },
- {
- "OwnerId": "1d2dd",
- "Name": "name test",
- "Store":{
- "StoreId": "ddf22",
- "Name": "Store2",
- "Code": "ble",
- "Machine":{
- "MachineId": "axdso",
- "Name": "Machine3",
- "Type": "type3"
- }
- }
- }]
- SELECT "owner"."id",
- json_agg(DISTINCT "store".*) AS "stores",
- json_agg(DISTINCT "machine".*) AS "machines"
- FROM "owners"
- INNER JOIN "stores"
- ON "stores"."ownerId" = "owners"."id"
- INNER JOIN "machines"
- ON "machines"."storeId" = "stores"."id"
- WHERE "owner" = 1
- GROUP BY "owner"."id";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement