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" |