Search code examples
sqlpostgresqljsonb

Create jsonb data with SQL query (postgresql), then create new column with this jsonb data


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:

  1. Above table is made up, just as an example.
  2. data_lable* and data_value* could be null

******* 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

Solution

  • 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
    )