How to extract different json elements from the same table in SQL query?

I am querying from a table with the following format:

id|provider|score
--------------------------------
1 |  att     | '{"attscore":300}'
1 |  verizon | '{"verizonscore":299}'
2 |   att    | '{"attscore":200}'
3 |  verizon | '{"verizonscore":155}'

I am trying to get a table that looks like the following:

id|attscore|verizonscore
-------------------------
1  |  300   |    299
2  |  200   |    null
3  |  null  |    155

Note that used to json in sql

Answer

CREATE TABLE table1 (
  `id` INTEGER,
  `provider` VARCHAR(7),
  `score` VARCHAR(22)
);

INSERT INTO table1
  (`id`, `provider`, `score`)
VALUES
  ('1', 'att', '{"attscore":300}'),
  ('1', 'verizon', '{"verizonscore":299}'),
  ('2', 'att', '{"attscore":200}'),
  ('3', 'verizon', '{"verizonscore":155}');

SELECT 
id,
GROUP_CONCAT(CASE WHEN provider = 'att' THEN `score`->"$.attscore" ELSe NULL END) attscore
,GROUP_CONCAT(CASE WHEN provider = 'verizon' THEN `score`->"$.verizonscore" ELSe NULL END) verizonscore
FROM table1
GROUP BY id

id | attscore | verizonscore
-: | :------- | :-----------
 1 | 300      | 299         
 2 | 200      | null        
 3 | null     | 155         

db<>fiddle here

This works with a fixed number of column quite well, if you have much more of these you need to do something like this