Complex MySql query: grouping and filtering in a query?

Question

MySQL

Good afternoon.
I practice working with the Rest API using a real example.
there is a game server, the task is to make a database for the police, with a search for users and their data. Probably typical CRUD
Base - Mysql 8, api generated via xmysql
Due to the fact that user data is in different tables, I came to the conclusion that you need to build a query through join.
Request example

axios.get('http://***:3000/api/xjoin?_join=user.users,_lj,vehicle.owned_vehicles,_lj,property.owned_properties,_lj&_on1=(user.identifier,eq,vehicle.owner)&_on2=(user.identifier,eq,property.owner)&_fields=user.firstname,user.lastname,user.dateofbirth,user.phone_number,user.job,property.name,vehicle.plate,vehicle.type,vehicle.job&_size=1000')
          .then(function (response) {
              console.log(response)
          })

Answer example:

{
        "user_firstname": "A",
        "user_lastname": "B",
        "user_dateofbirth": "30",
        "user_phone_number": "42147",
        "user_job": "mechanic",
        "property_name": "NorthConkerAvenue2044",
        "vehicle_plate": "UVD 395",
        "vehicle_type": "helicopter",
        "vehicle_job": "ambulance"
    },
{
        "user_firstname": "A",
        "user_lastname": "B",
        "user_dateofbirth": "30",
        "user_phone_number": "42147",
        "user_job": "mechanic",
        "property_name": "Modern2Apartment",
        "vehicle_plate": "UVD 395",
        "vehicle_type": "helicopter",
        "vehicle_job": "ambulance"
    },
{
        "user_firstname": "C",
        "user_lastname": "D",
        "user_dateofbirth": "27",
        "user_phone_number": "15607",
        "user_job": "taxi",
        "property_name": null,
        "vehicle_plate": null,
        "vehicle_type": null,
        "vehicle_job": null
    },

Because I'm not very strong in mysql, I would even say not very strong, I had the following questions:
1) Is it possible to group "property_name" and similar keys with different values ​​into one, so as not to produce a bunch of identical objects? And how to do it right. Google found gorup_concat, but did not understand how to make friends with my requirements. The api has support for dynamic query, where, in fact, you can insert it by rewriting the query to the syntax of mysql itself
2) How to filter out irrelevant answers?
For example, there is "vehicle_type": "helicopter", but I need type: car or null if there is no car. And if there is a car, then "vehicle_job" should be null. Same with properties, group into one if multiple or return null,

&_where=(vehicle.type,like,car)~and(vehicle.job,is,null)

I'm losing the right users
I would be grateful for any help
UPD: tiny.cc/o9thez - database with data for an example

Answer 1

it makes sense to first bring the SQL query to a normal state, and then move on to the api.
Total - there are users, there is property, there is transport.
1. There is a certain incomprehensibility - if we grouped by user, he has several properties in his possession and several vehicles - how many rows do you want to get in the end?

SELECT user.*,  
   GROUP_CONCAT(DISTINCT property.name ORDER BY property.name ASC SEPARATOR ', ') AS propertys,
....
   GROUP BY user.identifier ;

This should give a list of property, but transport is not so easy to do - there are 3 fields, this is already on the verge of writing a stored procedure
2. It is enough to display only venicle_plate, provided that:
in this case group_concat will do the trick.
Offhand, you can also do nested subqueries:

SELECT id,
 (SELECT GROUP_CONCAT(DISTINCT name  ORDER BY name ASC SEPARATOR ', ') FROM owned_properties WHERE users.identifier = owned_properties.owner GROUP BY owned_properties.owner ) as property,
 (SELECT GROUP_CONCAT(DISTINCT plate ORDER BY plate ASC SEPARATOR ', ') FROM owned_vehicles  
      WHERE job is null AND (type is null or type='car') AND 
      users.identifier = owned_vehicles .owner GROUP BY owned_vehicles .owner ) as venicle
  FROM users;

again, it looks like it would be more convenient to have a document-oriented noSQL database of
an example database at hand to debug queries, but it looks viable.