Advertisement
Guest User

Untitled

a guest
Jun 17th, 2019
61
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.66 KB | None | 0 0
  1. SELECT ow.*, st.*, ma.*
  2. FROM owner ow
  3. INNER JOIN st.store ON ow.OwnerId = st.OwnerId
  4. INNER JOIN machine ma ON ma.StoreId = st.StoreId;
  5.  
  6. {
  7. "OwnerId": "1d2dd",
  8. "Name": "name test",
  9. "Store":[{
  10. "StoreId": "s3ss5",
  11. "Name": "Store1",
  12. "Code": "bla",
  13. "Machine":[{
  14. "MachineId": "axpeo",
  15. "Name": "Machine1",
  16. "Type": "type1"
  17. }]
  18. },
  19. {
  20. "StoreId": "ddf22",
  21. "Name": "Store2",
  22. "Code": "ble",
  23. "Machine":[{
  24. "MachineId": "weds",
  25. "Name": "Machine2",
  26. "Type": "type2"
  27. },
  28. {
  29. "MachineId": "axdso",
  30. "Name": "Machine3",
  31. "Type": "type3"
  32. }]
  33. }]
  34. }
  35.  
  36. SELECT json_build_object('OwnerId', ownerid,
  37. 'Name', name,
  38. 'Store', stores)
  39. FROM owner
  40. JOIN (
  41. SELECT ownerid,
  42. json_agg(
  43. json_build_object('StoreId', storeid,
  44. 'Name', name,
  45. 'Code', code,
  46. 'Machine', machines)) AS stores
  47. FROM store
  48. JOIN (
  49. SELECT storeid,
  50. json_agg(
  51. json_build_object('MachineId', machineid,
  52. 'Name', name,
  53. 'Type', type)) AS machines
  54. FROM machine
  55. GROUP BY storeid) m USING (storeid)
  56. GROUP BY ownerid) s USING (ownerid);
  57.  
  58. [{
  59. "OwnerId": "1d2dd",
  60. "Name": "name test",
  61. "Store":{
  62. "StoreId": "s3ss5",
  63. "Name": "Store1",
  64. "Code": "bla",
  65. "Machine":{
  66. "MachineId": "axpeo",
  67. "Name": "Machine1",
  68. "Type": "type1"
  69. }
  70. }
  71. },
  72. {
  73. "OwnerId": "1d2dd",
  74. "Name": "name test",
  75. "Store":{
  76. "StoreId": "ddf22",
  77. "Name": "Store2",
  78. "Code": "ble",
  79. "Machine":{
  80. "MachineId": "weds",
  81. "Name": "Machine2",
  82. "Type": "type2"
  83. }
  84. }
  85.  
  86. },
  87. {
  88. "OwnerId": "1d2dd",
  89. "Name": "name test",
  90. "Store":{
  91. "StoreId": "ddf22",
  92. "Name": "Store2",
  93. "Code": "ble",
  94. "Machine":{
  95. "MachineId": "axdso",
  96. "Name": "Machine3",
  97. "Type": "type3"
  98. }
  99. }
  100. }]
  101.  
  102. SELECT "owner"."id",
  103. json_agg(DISTINCT "store".*) AS "stores",
  104. json_agg(DISTINCT "machine".*) AS "machines"
  105. FROM "owners"
  106. INNER JOIN "stores"
  107. ON "stores"."ownerId" = "owners"."id"
  108. INNER JOIN "machines"
  109. ON "machines"."storeId" = "stores"."id"
  110. WHERE "owner" = 1
  111. GROUP BY "owner"."id";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement