我有一个巨大的sql查询,其中有一个select行:
COALESCE(JSONB_AGG(JSONB_BUILD_OBJECT('id', tiw.topicwordid, 'topic', tw.topic, 'wikilink', tw.wikilink)) FILTER (WHERE tiw.topicwordid IS NOT NULL), '[]') as TopicWords
在JSON\u BUILD\u对象中,我希望有一列“count”。在这里,我想计算一个对象在下面的列表中出现的次数。然后我想区分这个列表,这样每个对象只出现一次。
现在的结果如下:
[{
"topic": "Automotive technologies",
"wikilink": "http://en.wikipedia.org/Category:Automotive_technologies",
"id": 1411
},{
"topic": "Government incentives for plug-in electric vehicles",
"wikilink": "http://en.wikipedia.org/Government_incentives_for_plug-in_electric_vehicles",
"id": 1412
},{
"topic": "Electric car",
"wikilink": "http://en.wikipedia.org/Electric_car",
"id": 932
},{
"topic": "Electric vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicle_manufacturers",
"id": 1413
},{
"topic": "Cars",
"wikilink": "http://en.wikipedia.org/Category:Cars_by_country",
"id": 1414
},{
"topic": "Electric vehicle industry",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicle_industry",
"id": 1415
},{
"topic": "Automobiles",
"wikilink": "http://en.wikipedia.org/Category:Automobiles",
"id": 1084
},{
"topic": "Transport",
"wikilink": "http://en.wikipedia.org/Category:Transport",
"id": 939
},{
"topic": "Wheeled vehicles",
"wikilink": "http://en.wikipedia.org/Category:Wheeled_vehicles",
"id": 1408
},{
"topic": "Tesla Model S",
"wikilink": "http://en.wikipedia.org/Tesla_Model_S",
"id": 1476
},{
"topic": "Motor vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Motor_vehicle_manufacturers",
"id": 1425
},{
"topic": "Electric vehicles",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicles",
"id": 1402
},{
"topic": "Automobile models",
"wikilink": "http://en.wikipedia.org/Category:Automobile_models",
"id": 1427
},{
"topic": "Aerodynamics",
"wikilink": "http://en.wikipedia.org/Aerodynamics",
"id": 1886
},{
"topic": "Product introductions",
"wikilink": "http://en.wikipedia.org/Category:Product_introductions_by_year",
"id": 1517
},{
"topic": "Sports cars",
"wikilink": "http://en.wikipedia.org/Category:Sports_cars",
"id": 1423
},{
"topic": "Sedans",
"wikilink": "http://en.wikipedia.org/Category:Sedans",
"id": 1436
},{
"topic": "Artificial objects",
"wikilink": "http://en.wikipedia.org/Category:Artificial_objects",
"id": 1030
},{
"topic": "Private transport",
"wikilink": "http://en.wikipedia.org/Category:Private_transport",
"id": 1400
},{
"topic": "Automotive technologies",
"wikilink": "http://en.wikipedia.org/Category:Automotive_technologies",
"id": 1411
},{
"topic": "Vehicle technology",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_technology",
"id": 1407
},{
"topic": "Transportation engineering",
"wikilink": "http://en.wikipedia.org/Category:Transportation_engineering",
"id": 1273
},{
"topic": "Car body styles",
"wikilink": "http://en.wikipedia.org/Category:Car_body_styles",
"id": 1419
},{
"topic": "Transport",
"wikilink": "http://en.wikipedia.org/Category:Transport",
"id": 939
},{
"topic": "Automobile layouts",
"wikilink": "http://en.wikipedia.org/Category:Automobile_layouts",
"id": 1420
},{
"topic": "Land vehicles",
"wikilink": "http://en.wikipedia.org/Category:Land_vehicles",
"id": 1410
},{
"topic": "Wheeled vehicles",
"wikilink": "http://en.wikipedia.org/Category:Wheeled_vehicles",
"id": 1408
},{
"topic": "Automotive industry",
"wikilink": "http://en.wikipedia.org/Category:Automotive_industry",
"id": 1403
},{
"topic": "Vehicles",
"wikilink": "http://en.wikipedia.org/Category:Vehicles",
"id": 1401
},{
"topic": "Motor vehicles",
"wikilink": "http://en.wikipedia.org/Category:Motor_vehicles",
"id": 1086
},{
"topic": "Automobiles",
"wikilink": "http://en.wikipedia.org/Category:Automobiles",
"id": 1084
},{
"topic": "Rear-wheel-drive vehicles",
"wikilink": "http://en.wikipedia.org/Category:Rear-wheel-drive_vehicles",
"id": 1712
},{
"topic": "Electric vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicle_manufacturers",
"id": 1413
},{
"topic": "Cars of the United States",
"wikilink": "http://en.wikipedia.org/Category:Cars_of_the_United_States",
"id": 1710
},{
"topic": "Cars",
"wikilink": "http://en.wikipedia.org/Category:Cars_by_country",
"id": 1414
},{
"topic": "Tesla Model S",
"wikilink": "http://en.wikipedia.org/Tesla_Model_S",
"id": 1476
},{
"topic": "Fuel economy in automobiles",
"wikilink": "http://en.wikipedia.org/Fuel_economy_in_automobiles",
"id": 1749
},{
"topic": "Tesla, Inc.",
"wikilink": "http://en.wikipedia.org/Tesla,_Inc.",
"id": 1444
},{
"topic": "Car",
"wikilink": "http://en.wikipedia.org/Car",
"id": 1457
},{
"topic": "Charging station",
"wikilink": "http://en.wikipedia.org/Charging_station",
"id": 1736
},{
"topic": "Technology",
"wikilink": "http://en.wikipedia.org/Category:Technology",
"id": 1066
},{
"topic": "Private transport",
"wikilink": "http://en.wikipedia.org/Category:Private_transport",
"id": 1400
},{
"topic": "Energy",
"wikilink": "http://en.wikipedia.org/Energy",
"id": 1010
},{
"topic": "Green vehicles",
"wikilink": "http://en.wikipedia.org/Category:Green_vehicles",
"id": 1409
},{
"topic": "Automotive technologies",
"wikilink": "http://en.wikipedia.org/Category:Automotive_technologies",
"id": 1411
},{
"topic": "Manufactured goods",
"wikilink": "http://en.wikipedia.org/Category:Manufactured_goods",
"id": 1516
},{
"topic": "Artificial objects",
"wikilink": "http://en.wikipedia.org/Category:Artificial_objects",
"id": 1030
},{
"topic": "Low-carbon economy",
"wikilink": "http://en.wikipedia.org/Category:Low-carbon_economy",
"id": 1406
},{
"topic": "Land vehicles",
"wikilink": "http://en.wikipedia.org/Category:Land_vehicles",
"id": 1410
},{
"topic": "Electric power",
"wikilink": "http://en.wikipedia.org/Category:Electric_power",
"id": 1071
},{
"topic": "Vehicle electrification",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_electrification",
"id": 1405
},{
"topic": "Vehicle technology",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_technology",
"id": 1407
},{
"topic": "Transportation engineering",
"wikilink": "http://en.wikipedia.org/Category:Transportation_engineering",
"id": 1273
},{
"topic": "Sedans",
"wikilink": "http://en.wikipedia.org/Category:Sedans",
"id": 1436
},{
"topic": "Sports cars",
"wikilink": "http://en.wikipedia.org/Category:Sports_cars",
"id": 1423
},{
"topic": "Electric vehicle industry",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicle_industry",
"id": 1415
},{
"topic": "Automobiles",
"wikilink": "http://en.wikipedia.org/Category:Automobiles",
"id": 1084
},{
"topic": "Vehicle electrification",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_electrification",
"id": 1405
},{
"topic": "Motor vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Motor_vehicle_manufacturers",
"id": 1425
},{
"topic": "Zero-emissions vehicles",
"wikilink": "http://en.wikipedia.org/Category:Zero-emissions_vehicles",
"id": 1404
},{
"topic": "Car body styles",
"wikilink": "http://en.wikipedia.org/Category:Car_body_styles",
"id": 1419
},{
"topic": "Automobile layouts",
"wikilink": "http://en.wikipedia.org/Category:Automobile_layouts",
"id": 1420
},{
"topic": "Luxury motor vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Luxury_motor_vehicle_manufacturers",
"id": 1424
},{
"topic": "Electric vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicle_manufacturers",
"id": 1413
},{
"topic": "Cars",
"wikilink": "http://en.wikipedia.org/Category:Cars_by_country",
"id": 1414
},{
"topic": "Automotive technologies",
"wikilink": "http://en.wikipedia.org/Category:Automotive_technologies",
"id": 1411
},{
"topic": "Vehicle technology",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_technology",
"id": 1407
},{
"topic": "Luxury vehicles",
"wikilink": "http://en.wikipedia.org/Category:Luxury_vehicles",
"id": 1422
},{
"topic": "Electric vehicles",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicles",
"id": 1402
},{
"topic": "Transportation engineering",
"wikilink": "http://en.wikipedia.org/Category:Transportation_engineering",
"id": 1273
},{
"topic": "Transport",
"wikilink": "http://en.wikipedia.org/Category:Transport",
"id": 939
},{
"topic": "Land vehicles",
"wikilink": "http://en.wikipedia.org/Category:Land_vehicles",
"id": 1410
},{
"topic": "Wheeled vehicles",
"wikilink": "http://en.wikipedia.org/Category:Wheeled_vehicles",
"id": 1408
},{
"topic": "Automotive industry",
"wikilink": "http://en.wikipedia.org/Category:Automotive_industry",
"id": 1403
},{
"topic": "Motor vehicles",
"wikilink": "http://en.wikipedia.org/Category:Motor_vehicles",
"id": 1086
},{
"topic": "Vehicles",
"wikilink": "http://en.wikipedia.org/Category:Vehicles",
"id": 1401
},{
"topic": "Private transport",
"wikilink": "http://en.wikipedia.org/Category:Private_transport",
"id": 1400
},{
"topic": "Tesla Model S",
"wikilink": "http://en.wikipedia.org/Tesla_Model_S",
"id": 1476
},{
"topic": "Electric vehicle industry",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicle_industry",
"id": 1415
},{
"topic": "Automobiles",
"wikilink": "http://en.wikipedia.org/Category:Automobiles",
"id": 1084
},{
"topic": "Zero-emissions vehicles",
"wikilink": "http://en.wikipedia.org/Category:Zero-emissions_vehicles",
"id": 1404
},{
"topic": "Battery electric vehicles",
"wikilink": "http://en.wikipedia.org/Category:Battery_electric_vehicles",
"id": 1514
},{
"topic": "Transport",
"wikilink": "http://en.wikipedia.org/Category:Transport",
"id": 939
},{
"topic": "Elon Musk",
"wikilink": "http://en.wikipedia.org/Elon_Musk",
"id": 1468
},{
"topic": "Electric vehicles",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicles",
"id": 1402
},{
"topic": "Battery electric vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Battery_electric_vehicle_manufacturers",
"id": 1515
},{
"topic": "Travis Kalanick",
"wikilink": "http://en.wikipedia.org/Travis_Kalanick",
"id": 1714
},{
"topic": "Automotive industry",
"wikilink": "http://en.wikipedia.org/Category:Automotive_industry",
"id": 1403
},{
"topic": "Tesla, Inc.",
"wikilink": "http://en.wikipedia.org/Tesla,_Inc.",
"id": 1444
},{
"topic": "Electric car",
"wikilink": "http://en.wikipedia.org/Electric_car",
"id": 932
},{
"topic": "Motor vehicles",
"wikilink": "http://en.wikipedia.org/Category:Motor_vehicles",
"id": 1086
},{
"topic": "Tesla Model S",
"wikilink": "http://en.wikipedia.org/Tesla_Model_S",
"id": 1476
},{
"topic": "Electric vehicles",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicles",
"id": 1402
},{
"topic": "Vehicles",
"wikilink": "http://en.wikipedia.org/Category:Vehicles",
"id": 1401
},{
"topic": "Car",
"wikilink": "http://en.wikipedia.org/Car",
"id": 1457
},{
"topic": "Tesla, Inc.",
"wikilink": "http://en.wikipedia.org/Tesla,_Inc.",
"id": 1444
},{
"topic": "Charging station",
"wikilink": "http://en.wikipedia.org/Charging_station",
"id": 1736
},{
"topic": "Land vehicles",
"wikilink": "http://en.wikipedia.org/Category:Land_vehicles",
"id": 1410
},{
"topic": "Mechanical engineering",
"wikilink": "http://en.wikipedia.org/Category:Mechanical_engineering",
"id": 1933
},{
"topic": "Transport",
"wikilink": "http://en.wikipedia.org/Category:Transport",
"id": 939
},{
"topic": "Technology",
"wikilink": "http://en.wikipedia.org/Category:Technology",
"id": 1066
},{
"topic": "Electric vehicles",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicles",
"id": 1402
},{
"topic": "Motor vehicles",
"wikilink": "http://en.wikipedia.org/Category:Motor_vehicles",
"id": 1086
},{
"topic": "Artificial objects",
"wikilink": "http://en.wikipedia.org/Category:Artificial_objects",
"id": 1030
},{
"topic": "Manufactured goods",
"wikilink": "http://en.wikipedia.org/Category:Manufactured_goods",
"id": 1516
},{
"topic": "Automotive industry",
"wikilink": "http://en.wikipedia.org/Category:Automotive_industry",
"id": 1403
},{
"topic": "Automobiles",
"wikilink": "http://en.wikipedia.org/Category:Automobiles",
"id": 1084
},{
"topic": "Automotive technologies",
"wikilink": "http://en.wikipedia.org/Category:Automotive_technologies",
"id": 1411
},{
"topic": "Transportation engineering",
"wikilink": "http://en.wikipedia.org/Category:Transportation_engineering",
"id": 1273
},{
"topic": "Vehicle technology",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_technology",
"id": 1407
},{
"topic": "Vehicles",
"wikilink": "http://en.wikipedia.org/Category:Vehicles",
"id": 1401
},{
"topic": "Wheeled vehicles",
"wikilink": "http://en.wikipedia.org/Category:Wheeled_vehicles",
"id": 1408
},{
"topic": "Private transport",
"wikilink": "http://en.wikipedia.org/Category:Private_transport",
"id": 1400
},{
"topic": "Engine",
"wikilink": "http://en.wikipedia.org/Engine",
"id": 1504
},{
"topic": "Machines",
"wikilink": "http://en.wikipedia.org/Category:Machines",
"id": 1934
},{
"topic": "Internal combustion engine",
"wikilink": "http://en.wikipedia.org/Internal_combustion_engine",
"id": 1935
},{
"topic": "Private transport",
"wikilink": "http://en.wikipedia.org/Category:Private_transport",
"id": 1400
},{
"topic": "Automobiles",
"wikilink": "http://en.wikipedia.org/Category:Automobiles",
"id": 1084
},{
"topic": "Vehicles",
"wikilink": "http://en.wikipedia.org/Category:Vehicles",
"id": 1401
},{
"topic": "Electric vehicles",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicles",
"id": 1402
},{
"topic": "Automotive industry",
"wikilink": "http://en.wikipedia.org/Category:Automotive_industry",
"id": 1403
},{
"topic": "Motor vehicles",
"wikilink": "http://en.wikipedia.org/Category:Motor_vehicles",
"id": 1086
},{
"topic": "Zero-emissions vehicles",
"wikilink": "http://en.wikipedia.org/Category:Zero-emissions_vehicles",
"id": 1404
},{
"topic": "Vehicle electrification",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_electrification",
"id": 1405
},{
"topic": "Transport",
"wikilink": "http://en.wikipedia.org/Category:Transport",
"id": 939
},{
"topic": "Low-carbon economy",
"wikilink": "http://en.wikipedia.org/Category:Low-carbon_economy",
"id": 1406
},{
"topic": "Vehicle technology",
"wikilink": "http://en.wikipedia.org/Category:Vehicle_technology",
"id": 1407
},{
"topic": "Wheeled vehicles",
"wikilink": "http://en.wikipedia.org/Category:Wheeled_vehicles",
"id": 1408
},{
"topic": "Transportation engineering",
"wikilink": "http://en.wikipedia.org/Category:Transportation_engineering",
"id": 1273
},{
"topic": "Green vehicles",
"wikilink": "http://en.wikipedia.org/Category:Green_vehicles",
"id": 1409
},{
"topic": "Electric power",
"wikilink": "http://en.wikipedia.org/Category:Electric_power",
"id": 1071
},{
"topic": "Land vehicles",
"wikilink": "http://en.wikipedia.org/Category:Land_vehicles",
"id": 1410
}]
所以结果应该是这样的:
[{
"topic": "Automotive technologies",
"wikilink": "http://en.wikipedia.org/Category:Automotive_technologies",
"id": 1411,
"count": 3
},{
"topic": "Government incentives for plug-in electric vehicles",
"wikilink": "http://en.wikipedia.org/Government_incentives_for_plug-in_electric_vehicles",
"id": 1412,
"count": 5
},{
"topic": "Electric car",
"wikilink": "http://en.wikipedia.org/Electric_car",
"id": 932,
"count": 3
},{
"topic": "Electric vehicle manufacturers",
"wikilink": "http://en.wikipedia.org/Category:Electric_vehicle_manufacturers",
"id": 1413
},{
"topic": "Cars",
"wikilink": "http://en.wikipedia.org/Category:Cars_by_country",
"id": 1414,
"count": 2
}]
