CODEWITHSUNDEEP
node.jsnode-postgres
ppb
ppb

Reputation: 2613

Fetch jsonb column of postgres db

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?

Upvotes: 1

Views: 798

Answers (1)

yen
yen

Reputation: 2292

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.

Upvotes: 1

Related Questions