I want to add a new column to the table with jsonb data(query data from other columns ).
Current table:
student_id | first_name | last_name | data_lable1 | data_value1 | data_lable2 | data_value2 | data_lable3 | data_value3 | … |
---|---|---|---|---|---|---|---|---|---|
1234 | Steve | Lee | address code | 106 | Grade | 8 | Pick up | true | … |
1235 | John | Lee | Transferred | true | Grade | 7 | Pick up | false | … |
After query data from data_lable1, data_value1, data_lable2, data_value2, data_lable3, data_value3, then add the jsonb data to new column student data:
student_id | first_name | last_name | data_lable1 | data_value1 | data_lable2 | data_value2 | data_lable3 | data_value3 | student data | … |
---|---|---|---|---|---|---|---|---|---|---|
1234 | Steve | Lee | area code | 106 | Grade | 8 | Pick up | true | {“area code”:”106”, “Grade”:”8”, “Pick up”: “true”} | … |
1235 | John | Lee | Transferred | true | Grade | 7 | Pick up | false | {“Transferred”:”true”, “Grade”:”8”, “Pick up”: “false”} | … |
I am wondering if this is doable with SQL query. If the answer is yes, how?
NOTE:
******* Update 1 *********
I am using jsonb_build_object(suggested by @Nnaemeka Daniel John) to build the jsonb object first.
select jsonb_build_object(CONCAT('',student.data_lable1,''), CONCAT('',student.data_value1,''), CONCAT('',student.data_lable2,''), CONCAT('',student.data_value2,''), CONCAT('',student.data_lable3,''), CONCAT('',student.data_value3,'')) from student;
I am getting a result, but seems in some cases the order could be wrong. Is there a way that I can set the data set sequence as the original sequence in the table?
{“Pick up”: “true”, “area code”:”106”, “Grade”:”8”}
Which really should be like below
{“area code”:”106”, “Grade”:”8”, “Pick up”: “true”}
BTW, I have to use CONCAT('',column_name,''), otherwise will get
ERROR: argument 1: key must not be null
With the jsonb_build_object
function you can build a jsonb object from the table columns for each row in table:
UPDATE your_table_name
SET student_data = jsonb_build_object(
data_label1, data_value1,
data_label2, data_value2,
data_label3, data_value3
)