Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
988 views
in Technique[技术] by (71.8m points)

postgresql - Split a JSON column into multiple columns in Postgres

I have a Postgres table signal which has a JSON column called signal_range and I want to split the range into different columns in a SELECT query.

Brand                         signal_range 
 AB    {"Weak": {"low": 15, "high": 120}, "Strong": {"low": null, "high": -240}, "Average": {"low": -240, "high": 15}}
 CA      
 FZ    {"Weak": {"low": 10, "high": 100}, "Strong": {"low": null, "high": -200}, "Average": {"low": -200, "high": 10}}

Expected output

Brand    Weak_low   Weak_high  Strong_low Strong_high  Average_low  Average_high
 AB      15           120                    -240         -240        15
 CA     
 FZ      10           100                    -200         -200        10

Is there anyway we can get this output in a select query?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This is one way:

 select brand, 
    signal_range#>'{Weak,low}' weak_low,signal_range#>'{Weak,high}' weak_high,
    signal_range#>'{Strong,low}' strong_low,signal_range#>'{Strong,high}' strong_high,
    signal_range#>'{Average,low}' average_low,signal_range#>'{Average,high}' average_high 
 from signal

Mind upper-/lowercase. If you have uppercase in column names you need to adjust for that with "" around the names. I really recommend all lowercase names.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...