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}

Given the way you have stored the JSON, there isn't an easy way to do this in an SQL expression. I suggest you fetch the whole JSON document back to a client application and write code to present it however you want. The alternative is to change the way you are storing the JSON document to make it easier to query. — Bill Karwin, Jan 19 '23 at 19:42
Does properties names (i.e. `"sng_ecommerce_purchase_revenue"` and `"7d"`) are static and known, or they must be queried dynamically? — Akina, Jan 20 '23 at 05:30

O'tkir Xo'jayev · Answer 1 · 2023-01-19T20:20:28.067

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

edited Jan 19 '23 at 20:20

answered Jan 19 '23 at 20:14

O'tkir Xo'jayev

83
9

1

Solution 2 works fine with SQL. But when I try to use this in app script (to auto update google sheets), they don't allow "7d", double quotes are not allowed. Any solution here? – King Jan 27 '23 at 13:26
"SELECT JSON_EXTRACT(XYZ, '$.sng_ecommerce_purchase_revenue.\"7d\"') as PR FROM singular_reports_table" pls try \" ,,, – O'tkir Xo'jayev Jan 27 '23 at 16:05

Nested JSON using MySQL

1 Answers1