Fetch jsonb column of postgres db

Question

I am using node-postgres to select and insert data into postgres. I have some column of jsonb type which I am fetching from db by using below query

getEmployee() {
 return SELECT empId, empData FROM employee WHERE empId = $1;
}

where empData is jsonb type of column. Below is code snippet which use above query.

const employee = await DBService.query(pgObj.getEmployee(), [empId]);

when I am trying to get empData from employee I am getting empty value.

 const { empData } = employee;

I am not sure what I am missing here. Is this the correct way to fetch josnb column of postgreas db in nodejs?

yen · Accepted Answer · 2021-09-19 00:03:03Z

1

Are you sure empdata is even populated, in the database? Maybe it's empty. Also, what are the jsonb fields of empdata?

To get the actual sub-fields of empdata, you need the ->> operator. eg:

get the whole json object as text

SELECT empId, empData::text
FROM employee where empId = $1

get individual attributes

SELECT empId, empData->>annual_pay as salary
FROM employee WHERE empId = $1;

etc... You can also try Have a look here: https://kb.objectrocket.com/postgresql/how-to-query-a-postgres-jsonb-column-1433

I haven't tried these out, I'm not in front of postgres right now.

edited Sep 19, 2021 at 0:03

answered Sep 18, 2021 at 23:37

yen

2,4224 gold badges27 silver badges60 bronze badges

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

2 Comments

ppb Over a year ago

Hi yen, thank you for your response. empdata is populating in db and it has json value. When I am trying to print empdata in logs it prints nothing. Can we fetch whole empdata object from db? or need to fetch sub-fields only?

yen Over a year ago

empdata is jsonb, it's not text; I don't think you can just print out. You need the text formatted values. So either use ->> for each field you need, or you need to cast the whole object to a text type (select empdata::text).

Collectives™ on Stack Overflow

Fetch jsonb column of postgres db

1 Answer 1

get the whole json object as text

get individual attributes

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

get the whole json object as text

get individual attributes

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related