Nested JSON using MySQL

Question

I am trying to use MySQL to query a column 'XYZ' and the value of column 'XYZ' is like a nested JSON as shown below

{
 "sng_ecommerce_purchase_revenue": {"7d": 18},
 "unique_sng_content_view": {"7d": 25},
 "Unique_login_send_otp": {"7d": 22.0},
 "Unique_conversation_clicked": {"7d": 8.0},
 "Unique_sng_ecommerce_purchase": {"7d": null},
 "Unique_sng_login": {"7d": 20.0}
}

Desired output:

sng_ecommerce_purchase_revenue-7d: 18
unique_sng_content_view-7d: 25
Unique_login_send_otp-7d: 22
Unique_conversation_clicked-7d: 8

My query:

select json_extract(XYZ, '$.sng_ecommerce_purchase_revenue') as pr
from singular_reports_table

Output is

{"7d": 18}

Answer 1

This is not a complete solution to use:

Solution #1:

SELECT 
    CONCAT('sng_ecommerce_purchase_revenue',
          REPLACE(REPLACE(REPLACE(JSON_EXTRACT(XYZ, '$.sng_ecommerce_purchase_revenue'),'{"','-'),'"',''),'}','')
     ) as PR
FROM singular_reports_table;

Solution #2:

SELECT 
      JSON_EXTRACT(XYZ, '$.sng_ecommerce_purchase_revenue."7d"') as PR
FROM singular_reports_table

helped