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
– Bill Karwin, Commented Jan 19, 2023 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
– Akina, Commented Jan 20, 2023 at 5:30

Utkir Khujaev · Accepted Answer · 2023-01-19 20:20:28Z

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, 2023 at 20:20

answered Jan 19, 2023 at 20:14

Utkir Khujaev

939 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

King Over a year ago

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?

Utkir Khujaev Over a year ago

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

Collectives™ on Stack Overflow

Nested JSON using MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related