MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table obj_table (
  object_id INT,
  field_name VARCHAR(12),
  value VARCHAR(12)
);

insert into obj_table values (1, 'latitude', '23.00001');
insert into obj_table values (1, 'longitude', '45.23445');
insert into obj_table values (1, 'category', 'office');
insert into obj_table values (2, 'latitude', '13.21451');
insert into obj_table values (2, 'longitude', '25.34355');
insert into obj_table values (2, 'category', 'center');
select * from obj_table;


object_id
field_name
value


1
latitude
23.00001

1
longitude
45.23445

1
category
office

2
latitude
13.21451

2
longitude
25.34355

2
category
center


select
    lng.object_id,
    ltd.value as latitude,
    lng.value as longitude
  from obj_table lng, obj_table ltd
  where lng.field_name = 'longitude'
    and ltd.field_name = 'latitude'
    and lng.object_id = ltd.object_id
;


object_id
latitude
longitude


1
23.00001
45.23445

2
13.21451
25.34355


select
    object_id,
    max(case field_name when 'latitude' then value else null end) as latitude,
    max(case field_name when 'longitude' then value else null end) as latitude,
    max(case field_name when 'category' then value else null end) as category
  from obj_table
  group by object_id
;


object_id
latitude
latitude
category


1
23.00001
45.23445
office

2
13.21451
25.34355
center


-- собираем в json:
select
    object_id,
	cast(replace(group_concat(json_object(field_name,value)),'},{',',') as json) json_obj
  from obj_table
  group by object_id
;

-- или так:
select
    object_id,
    cast(concat('{',group_concat( concat('"',field_name,'":"',value,'"') separator ',' ), '}') as json) as json_obj
  from obj_table
  group by object_id
;


object_id
json_obj


1
{"category": "office", "latitude": "23.00001", "longitude": "45.23445"}

2
{"category": "center", "latitude": "13.21451", "longitude": "25.34355"}




object_id
json_obj


1
{"category": "office", "latitude": "23.00001", "longitude": "45.23445"}

2
{"category": "center", "latitude": "13.21451", "longitude": "25.34355"}


-- извлекаем поля из json:
select
    object_id,
    json_extract(json_obj,'$.latitude') as latitude,
    json_extract(json_obj,'$.longitude') as latitude,
    json_extract(json_obj,'$.category') as category
  from (
select
    object_id,
    cast(replace(group_concat(json_object(field_name,value)),'},{',',') as json) json_obj
  from obj_table
  group by object_id
  ) t
;


object_id
latitude
latitude
category


1
"23.00001"
"45.23445"
"office"

2
"13.21451"
"25.34355"
"center"

object_id	field_name	value
1	latitude	23.00001
1	longitude	45.23445
1	category	office
2	latitude	13.21451
2	longitude	25.34355
2	category	center

object_id	json_obj
1	{"category": "office", "latitude": "23.00001", "longitude": "45.23445"}
2	{"category": "center", "latitude": "13.21451", "longitude": "25.34355"}