MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (
  pk int not null primary key auto_increment,
  js json
);
insert into t (js) values
('{
  "id": 123,
  "position": "North",
  "attributes": [{
    "name": "First Name",
    "value": "Santa"
  }, {
    "name": "Last Name",
    "value": "Claus"
  }]
}'),
('{
  "id": 456,
  "position": "North",
  "attributes": [{
    "name": "Last Name",
    "value": "Smith"
  }, {
    "name": "First Name",
    "value": "John"
  }]
}');

Records: 2  Duplicates: 0  Warnings: 0


select
    t.pk,
    jt.id,
    jt.position,
    min(case when jt.name = 'first name' then jt.value end) as fname,
    min(case when jt.name = 'last name' then jt.value end) as lname
from t
cross join json_table(
    t.js,
    '$' columns (
        id int path '$.id',
        position varchar(20) path '$.position',
        nested path '$.attributes[*]' columns (
            name varchar(20) path '$.name',
            value varchar(20) path '$.value'
        )
    )
) as jt
group by t.pk, jt.id, jt.position


pk
id
position
fname
lname


1
123
North
Santa
Claus

2
456
North
John
Smith


select
    t.pk,
    json_value(t.js, '$.id' returning signed) as id,
    json_value(t.js, '$.position' returning char(10)) as position,
    lj.*
from t, lateral (
    select
        min(case when name = 'first name' then value end) as fname,
        min(case when name = 'last name' then value end) as lname
    from json_table(
        t.js,
        '$.attributes[*]' columns (
            name varchar(20) path '$.name',
            value varchar(20) path '$.value'
        )
    ) as jt
) as lj


pk
id
position
fname
lname


1
123
North
Santa
Claus

2
456
North
John
Smith