PostgreSQL UPDATE column from JSONB value

Question

I have a postgresql table that has a column that contains the jsonb data for the row. Not all of the data was extracted from the jsonb to create the table but we wanted to store it in case we need to extract more data.

We now would like to pull one more field from the jsonb in the row and create a new column to store the value. The examples I have seen do not seem to address this specifically thus makes me want to double check my process and to see if there is an easier way to do this in PostgreSQL.

My insert statement looks like this:

insert into companyTable (company_id)
    select company_object ->>'CompanyId'
        from companyTable

This should then extract the value from the jsonb company_obejct and insert into the column company_id for each row in the table. Does this look correct and is their an easier way to accomplish this task?

UPDATE: Given @Łukasz Kamiński statement it appears that I want to UPDATE not insert as INSERT adds new row and I just want to update the row where the new column has been added. With that I believe this is the code I should be using.

update companyTable
set (company_id) =
    (select company_object ->>'CompanyId'
        from company.company)

Also, because I am not understanding fully the context of UPDATE should this be placed in a loop so it updates each row with that rows jsonb object?

for example (before update):

id | col1 | company_object           | company_id
---|------|--------------------------|------------
1  | a    |{'b':1, 'company_id': 3}  |  NULL
2  | a    |{'b':2, 'company_id': 4}  |  NULL
3  | a    |{'b':3, 'company_id': 5}  |  NULL

(after update)

id | col1 | company_object           | company_id
---|------|--------------------------|------------
1  | a    |{'b':1, 'company_id': 3}  |  3
2  | a    |{'b':2, 'company_id': 4}  |  4
3  | a    |{'b':3, 'company_id': 5}  |  5

Show us your complete json and tell us what you want to see in the target table after insertion. — Kaushik Nayak
– Kaushik Nayak, Commented Apr 15, 2019 at 5:29
This sounds like you want to do UPDATE. Or do you really want new rows in companyTable? — Łukasz Kamiński
– Łukasz Kamiński, Commented Apr 15, 2019 at 6:19
@ŁukaszKamiński I do not want a new row I want to add a value to the new column company_id of the existing rows. — Prof. Falken
– Prof. Falken, Commented Apr 15, 2019 at 12:32

score 3 · Accepted Answer · 2019-04-15 15:08:04Z

3

You don't need a sub-select for that:

update companyTable
  set company_id = (company_object ->>'company_id')::int;

If the JSON contains empty strings you need to "nullify" them:

update companyTable
  set company_id = nullif(trim(company_object ->>'company_id'), '')::int
where company_object ? 'company_id';

Online example: https://rextester.com/UXQKG95245

As no where clause is supplied this will update all rows in the table.

However, I don't see the reason to duplicate that information. Either store it in a column company_id or store it inside the JSON value, but duplicating it is most probably a bad idea because this might get out-of-sync quite easily.

Creating a view might be a better way to make this value easily accessible:

create view company_information
as
select id, col1, company_object, 
       company_object ->> 'company_id' as company_id
from company_table;

edited Apr 15, 2019 at 15:08

answered Apr 15, 2019 at 14:07

user330315

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

7 Comments

Prof. Falken Over a year ago

@a-horse-with-no-name The purpose is to have the data easily accessible to the data scientist who pull from this table. Given this ::int is this to cast it to an integer? Also, that code gives and invalid input syntax for integer error. Thanks for your help.

user330315 Over a year ago

@Prof.Falken: yes, ::int is a cast. If you get that error that means that not all JSON values contain a valid integer at that key. Are you aware that you need to update that column every time the data in the table changes? It might be easier to just create a view for the data scientiest

Prof. Falken Over a year ago

@a-horse-with-no-name The data gets refreshed quarterly and we will be adding these new columns to the refresh in the future. Unless I am missing something here. Concerning the error, there are null values for some entries in the jsonb, is there a way to get passed this error i.e. ignore null?

user330315 Over a year ago

@Prof.Falken: a real null value is OK, see here you might have empty strings instead (see my edit for a solution to deal with that). Or different keys (your sample data in the question uses company_id and CompanyId as the key.

Prof. Falken Over a year ago

@a-horse-with-no-name Some of the stored JSONb's do not have the company_id field thus it would not exist. Should I add a where statement? not to sure how to get around that. Also, thanks for the create view suggestion. I will speak with my team and see if this is something that can be pursued.

|

Collectives™ on Stack Overflow

PostgreSQL UPDATE column from JSONB value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related