JSON query for Board capabilities

-- One JSON object for a given board id like 'IS-3R-V2'
SELECT JSON_PRETTY(
  JSON_OBJECT(
    'boardType',     bt.code,
    'boardVersion',  bv.version_code,

    'module',
      (SELECT JSON_OBJECT('chip', m.chip, 'flash_size_mb', m.flash_size_mb)
         FROM board_modules m WHERE m.board_version_id = bv.id),

    'aliases',
      COALESCE(
        (SELECT JSON_ARRAYAGG(a.alias ORDER BY a.alias)
           FROM board_aliases a WHERE a.board_version_id = bv.id),
        JSON_ARRAY('')
      ),

    'capabilities',
      JSON_OBJECT(
        -- booleans → JSON true/false
        'has_mlx90614',       IF(bc.has_mlx90614      = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_bme688',         IF(bc.has_bme688        = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_sht41',          IF(bc.has_sht41         = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_ld2410_uart',    IF(bc.has_ld2410_uart   = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_ld2410_binary',  IF(bc.has_ld2410_binary = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_pairing_led',    IF(bc.has_pairing_led   = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_sensor_led',     IF(bc.has_sensor_led    = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_pairing_button', IF(bc.has_pairing_button= 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        'has_gnd_sw',         IF(bc.has_gnd_sw        = 1, JSON_EXTRACT('true','$'), JSON_EXTRACT('false','$')),
        -- numeric fields → pass as-is (they stay numeric in JSON)
        'num_lm35_sensors',    bc.num_lm35_sensors,
        'num_heating_circuits',bc.num_heating_circuits,
        'num_lighting_circuits',bc.num_lighting_circuits
      ),

    'pinout',
      JSON_OBJECT(
        'pairing_button_gpio', bp.pairing_button_gpio,
        'gnd_sw_gpio',         bp.gnd_sw_gpio,
        'heater_relay_1',      bp.heater_relay_1,
        'heater_relay_2',      bp.heater_relay_2,
        'heater_relay_3',      bp.heater_relay_3,
        'lights_relay_1',      bp.lights_relay_1,
        'lights_relay_2',      bp.lights_relay_2,
        'i2c_sda',             bp.i2c_sda,
        'i2c_scl',             bp.i2c_scl,
        'i2c_port',            bp.i2c_port,
        'mlx90614_addr',       bp.mlx90614_addr,
        'bme688_addr',         bp.bme688_addr,
        'sht41_addr',          bp.sht41_addr,
        'ld2410_tx',           bp.ld2410_tx,
        'ld2410_rx',           bp.ld2410_rx,
        'ld2410_out',          bp.ld2410_out,
        'ld2410_uart_port',    bp.ld2410_uart_port,
        'lm35_remote',         bp.lm35_remote,
        'lm35_main',           bp.lm35_main,
        'pairing_led',         bp.pairing_led,
        'sensor_led',          bp.sensor_led
      )
  )
) AS board_json
FROM board_versions bv
JOIN board_types bt            ON bt.id = bv.board_type_id
LEFT JOIN board_capabilities bc ON bc.board_version_id = bv.id
LEFT JOIN board_pinout      bp  ON bp.board_version_id = bv.id
WHERE bt.code='IS-3R' AND bv.version_code='V2';