Order by column with specific adjacent value
Basically Json makes the things, a bit more complicated, but not that much, but as you noticed, i don't think that it is necessary.
My approach as you need the data, is to use a pivot table.
This is actually flexible as it uses all, independent which are needed.
At the end is the query without the flexibility of automation. But then you have manually have to select the item you want.
Another approach is to transfor the values data into a json, bu then you need to extract all vital parts again. It wouldn't look that much prettier


CREATE TABLE items (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO items
  (`id`, `name`)
VALUES
  ('1', 'first-item'),
  ('2', 'second-item'),
  ('3', 'third-item');




CREATE TABLE fields (
  `id` INTEGER,
  `name` VARCHAR(11)
);

INSERT INTO fields
  (`id`, `name`)
VALUES
  ('1', 'title'),
  ('2', 'description'),
  ('3', 'available');




CREATE TABLE values1 (
  `id` INTEGER,
  `item_id` INTEGER,
  `field_id` INTEGER,
  `value` JSON
);




INSERT INTO values1
  (`id`, `item_id`, `field_id`, `value`)
VALUES
  ('1', '1', '1', '&quot;First Item&quot;')
  ,
  ('2', '1', '2', '&quot;This is the first item&quot;'),
  ('3', '1', '3', '&quot;1&quot;'),
  ('4', '2', '1', '&quot;Second Item&quot;'),
  ('5', '2', '2', '&quot;This is the second item&quot;'),
  ('6', '2', '3', '&quot;1&quot;'),
  ('7', '3', '1', '&quot;Third Item&quot;'),
  ('8', '3', '2', '&quot;This is the third item&quot;'),
  ('9', '3', '3', '&quot;0&quot;');




    SELECT 
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(CASE v.`field_id` WHEN &quot;',
         f.`id`,
          '&quot; THEN v.`value` ELSE NULL END) AS `',
          f.`name`), '`'      
        
        ORDER BY f.`id`
      ) 
      INTO @sql
    FROM `items` i
         left join 
         `values1` v 
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`;




SET @sql = CONCAT('select MAX(i.`name`), ',@sql,' from `items` i
         left join 
         `values1` v 
         on i.`id` = v.`item_id`
         left join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY title DESC');




PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;





MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | &quot;Second Item&quot; | &quot;This is the second item&quot; | &quot;1&quot;      
first-item    | &quot;First Item&quot;  | &quot;This is the first item&quot;  | &quot;1&quot;      





select 
MAX(i.`name`)
, MAX(CASE v.`field_id` WHEN &quot;1&quot; THEN v.`value` ELSE NULL END) AS `title`
,MAX(CASE v.`field_id` WHEN &quot;2&quot; THEN v.`value` ELSE NULL END) AS `description`
,MAX(CASE v.`field_id` WHEN &quot;3&quot; THEN v.`value` ELSE NULL END) AS `available` 
from `items` i
         LEFT  join 
         `values1` v 
         on i.`id` = v.`item_id`
         LEFT join `fields` f on v.`field_id` = f.`id`
group by i.`id`
HAVING `available` + 0 = 1
ORDER BY `title` DESC


MAX(i.`name`) | title         | description               | available
:------------ | :------------ | :------------------------ | :--------
second-item   | &quot;Second Item&quot; | &quot;This is the second item&quot; | &quot;1&quot;      
first-item    | &quot;First Item&quot;  | &quot;This is the first item&quot;  | &quot;1&quot;      


db&lt;&gt;fiddle here