These days it’s mysql. Because I need to synchronize the data in the company data center to our own mysql library, and use the way of fully synchronizing the SQL result set, I have been exposed to a large number of SQL usage scenarios when using the platform. Today encountered is how to use SQL spelling JSON, and share with you.

If the business itself can be implemented through API, then there is no need to implement it in SQL. Our business needs must be implemented in SQL, so we do so, you have no idea how much I miss the Java language, I miss the days of galloping in IDEA…

All right, let’s cut to the chase. The original table data test is given first

id name type num1 num2 num3 num4
1 Experimental primary school Primary school 1 2 3 4
2 Foreign language school High school 5 6 7 8
3 The seventh middle school Junior high school 9 10 11 12

Okay, and here’s what I ended up thinking:

id name type content
1 Experimental primary school Primary school {‘num1′:’1′,’num2′:’2′,’num3′:’3′,’num4′:’4’}
2 Foreign language school High school {‘num1′:’5′,’num2′:’6′,’num3′:’7′,’num4′:’8’}
3 The seventh middle school Junior high school {‘num1′:’9′,’num2′:’10’,’num3′:’11’,’num4′:’12’}

This watch is so hard to make, give me a thumbs up. The code word is not easy. I want the last few columns of data to be stored as JSON in the Content field (don’t ask me why, he does). Give the SQL for the query.


select 
    id, name, type,CONCAT(
        '{',
        CONCAT_WS(', ',
            CONCAT('"'.'num1'.'" : "', num1, '"'),
            CONCAT('"'.'num2'.'" : "', num2, '"'),
            CONCAT('"'.'num3'.'" : "', num3, '"'),
            CONCAT('"'.'num4'.'" : "', num4, '"')),'} '
)content from test
    
Copy the code

When we’re done, we’ll get what we want. All right, that’s it for today. Again, use language. Try not to use SQL unless you have to. Because it’s gross and slow and messy.